Importing to accsess from CSV file error

[dimitrijus99]

Hi,

have macro, which doing automatic file import to acsess DB. After I run the query it's showing me error (addind print screen). When pressing debug it's showing error on code line: DoCmd.TransferSpreadsheet acImport, , "TBL_ADD_VALUE_Genesys-temp", strfolder & "" & strFileName, , sheetname1
Other files are going well, just problem with this one. From where I should start checking?

Below adding whole part of code which are doing this importing step.

Case "ADD VALUE - Genesys"



'Check if file has already been imported
If DCount("Filename", "TBL_ADD_VALUE_Genesys_ImportedFiles", "Filename=""" & strFileName & """") = 0 Then
Dim sheetname1 As String
' now have here problem with macro, need separate sheet name withouth format because now excel didnt understand which sheet need to take
sheetname1 = Left(strFileName, 19)
'sheetname1 = Left(strFileName, (Application.(".", strFileName, 1) - 1))
DoCmd.TransferSpreadsheet acImport, , "TBL_ADD_VALUE_Genesys-temp", strfolder & "" & strFileName, , sheetname1

CurrentDb.Execute "INSERT INTO TBL_ADD_VALUE_Genesys_ImportedFiles (Filename) " & vbCrLf & _
"VALUES('" & strFileName & "')"
CurrentDb.Execute "APPEND_TBL_ADD_VALUE_Genesys"
CurrentDb.Execute "DELETE * FROM [TBL_ADD_VALUE_Genesys-temp]"
icounter_new = icounter_new + 1
strLastFileImported = strFileName
strLastFileImportedDate = Format(Now, "dd/MM/yyyy")
Else
icounter_existing = icounter_existing + 1
End If

[ranman256]

is the csv in text format? can u open it in notepad?
does the csv use the extension .CSV?
does the SPEC match the format of the .csv? (is the csv comma delimited, as well as the spec file using comma delim?)

[dimitrijus99]

Example.zip

Adding file example.
; delimited
" for text
Yes it is possible open with note pad.

I also tried in describe separate import specification in MSysIMEXSpecs but this also doesn't helped or Im doing something wrong in it. At file type tried with both values, 850 and 1252

DateDelim

DateFourDigitYear

DateLeadingZeros

DateOrder

DecimalPoint

FieldSeparator

FileType

SpecID

SpecName

SpecType

StartRow

TextDelim

TimeDelim

-

TRUE

TRUE

0

,

;

850

44

Genesys

1

1

"

:

[ranman256]

in the SPEC settings,

Are the fields using TRUE, set to TEXT? (since they are not boolean)

[dimitrijus99]

Sorry, just when copied to excel it was changed.

Real view in access table instead of TRUE there are -1 value.

[ssanfu]

In the thread title you wrote "Importing to accsess from CSV file error ", but then you are referencing an Excel sheetname. The file you posted has an extension of CSV, which is a text file. By default, Excel is set to open TXT/CSV files, but that does not mean they are spreadsheets.
My thought is that some of the variables are NULL in the command "DoCmd.TransferSpreadsheet" because a text file does not have a "sheetname".

DoCmd.TransferSpreadsheet acImport, , "TBL_ADD_VALUE_Genesys-temp", strfolder & "" & strFileName, , sheetname1

Also, there are several errors in the code snippet you posted.

Maybe is/was was unavoidable, but it is not good to have spaces in field names.

It would really help to see the dB and all of the code, but here is what I see...

.
.
.
Case "ADD VALUE - Genesys"

    'Check if file has already been imported
    If DCount("Filename", "TBL_ADD_VALUE_Genesys_ImportedFiles", "Filename=""" & strFileName & """") = 0 Then
        Dim sheetname1 As String      '<<-- Declarations should be at the top of the routine, NOT within any looping structure.
        ' now have here problem with macro, need separate sheet name withouth format because now excel didnt understand which sheet need to take
        sheetname1 = Left(strFileName, 19)
        'sheetname1 = Left(strFileName, (Application.(".", strFileName, 1) - 1))
        DoCmd.TransferSpreadsheet acImport, , "TBL_ADD_VALUE_Genesys-temp", strfolder & "" & strFileName, , sheetname1   '<<-- the RED text doesn't need to be there - 

        CurrentDb.Execute "INSERT INTO TBL_ADD_VALUE_Genesys_ImportedFiles (Filename) " & vbCrLf & _          '<<-- the vbCrLf shouldn't be there
                          "VALUES('" & strFileName & "')"
        CurrentDb.Execute "APPEND_TBL_ADD_VALUE_Genesys"
        CurrentDb.Execute "DELETE * FROM [TBL_ADD_VALUE_Genesys-temp]"
        icounter_new = icounter_new + 1
        strLastFileImported = strFileName
        strLastFileImportedDate = Format(Now, "dd/MM/yyyy")
    Else
        icounter_existing = icounter_existing + 1
    End If
.
.
.

Did you know "Example.csv" has a header row (field names), 1 row of data and 4657 lines of ";;;;;;;;;;;;;;;"?

[dimitrijus99]

Hello,

tried, to do changes as you wrote. Receiving same error. Yes, Example.csv always have the same header row and at others rows are data separated with ;
Also tried add import spec using import wizard and then described this import specification in code, but this also doesn't helped.

[dimitrijus99]

Guys, find out how solve this problem, changed a little bit code and it's working. Thank you for support. Adding changed code below, maybe this will help for someone Instead of acImport, changed to acImportDelim and as you said deleted sheet name value. Used same import specification which was generated through import wizard.

Case "ADD VALUE - Genesys"

'Check if file has already been imported
If DCount("Filename", "TBL_ADD_VALUE_Genesys_ImportedFiles", "Filename=""" & strFileName & """") = 0 Then



DoCmd.TransferText acImportDelim, "Genesys Import Specification", "TBL_ADD_VALUE_Genesys-temp", strfolder & "" & strFileName
CurrentDb.Execute "INSERT INTO TBL_ADD_VALUE_Genesys_ImportedFiles (Filename) " & vbCrLf & _
"VALUES('" & strFileName & "')"
CurrentDb.Execute "APPEND_TBL_ADD_VALUE_Genesys"
CurrentDb.Execute "DELETE * FROM [TBL_ADD_VALUE_Genesys-temp]"
icounter_new = icounter_new + 1
strLastFileImported = strFileName
strLastFileImportedDate = Format(Now, "dd/MM/yyyy")
Else
icounter_existing = icounter_existing + 1
End If

[ssanfu]

Happy you figured it out.

Note that you don't need

", strfolder & "" & strFileName

nor

mportedFiles (Filename) " & vbCrLf & _

It is best if you wrap the code in code tags. Hover the cursor over the # in the quick reply tool bar. Clicking on the # will insert the code tags - then paste the code between the tags.