auto importing csv (1 Viewer)

[jerry28ph]

I tried to copy a code that I found in this forum on how to auto import csv files into table, and something is wrong with my added code and an error message appear when im trying to run the code:

Runtime error: 3625

"The text file specification "0" does not exist. You cannot import, export and link using the specification."

I have day to day file to be imported into my table name "Tablogs"

****************
Private Sub Command0_Click()

Dim strFile As String 'Filename
Dim strFileList() As String 'File Array
Dim intFile As Integer 'File Number
Dim filename As String
Dim path As String

DoCmd.SetWarnings False
path = "c:\3difProject\csv files\"

'Loop through the folder & build file list
strFile = Dir(path & "*.csv")

While strFile <> ""
'add files to the list
intFile = intFile + 1
ReDim Preserve strFileList(1 To intFile)
strFileList(intFile) = strFile
strFile = Dir()
Wend

'see if any files were found
If intFile = 0 Then
MsgBox "No files found"
Exit Sub
End If

'cycle through the list of files
For intFile = 1 To UBound(strFileList)
filename = path & strFileList(intFile)
DoCmd.TransferText , acImportDelim, "Tablogs", filename, True

Next intFile

DoCmd.SetWarnings True

End Sub

**************************

Please tell me how to create a "SPECIFICATION" i dont how to do it.
Something is wrong with red line. Please advise if i need to add code or the syntax might wrong.

Please help me.

Thanks,JE

 

[Dennisk]

you have an extra comma

DoCmd.TransferText acImportDelim, "Tablogs", filename, True

 

[Kiwiman]

Howzit

To create an import spec, import a file manually.

• Rt Click anywhere in the object page white space and select import
• Navigate to file location and select file and correct file type
• Select the delimited format and then next
• Choose the delimiter type
• Select First Row contains field names (if the row headings are consistent for all your files
• Click Advanced
• Scroll down the field names checking the Data Type
• When you are happy with all the settings save the specification, make a note of the name so you can use in your import routine
• Test the import by importing into a staging table to ensure it works

Your final code for the transfer part will look like the below...

DoCmd.TransferText acImportDelim, "yourimportspec", "Tablogs", filename, True

 

[jerry28ph]

hi, ok just want to know after i click inside the object page and i selected import, what kind of file should i select, is it file that i will use to store the record which is ".csv" and did til the last procedure and I have my "tablogs spec" filename for the specification. Im confuse on when to use the TransferText and TransferSpreadsheet, I know spreadsheet and it should be save as *.xls", that's why i used the TransferText because im using *.csv" in which i'll be importing from other folder.

You mean setting up the specs will be necessary in order to access to know the field type i used to my table ("Tablogs") before the importing process?

Howzit

To create an import spec, import a file manually.

• Rt Click anywhere in the object page white space and select import
• Navigate to file location and select file and correct file type
• Select the delimited format and then next
• Choose the delimiter type
• Select First Row contains field names (if the row headings are consistent for all your files
• Click Advanced
• Scroll down the field names checking the Data Type
• When you are happy with all the settings save the specification, make a note of the name so you can use in your import routine
• Test the import by importing into a staging table to ensure it works

Your final code for the transfer part will look like the below...

DoCmd.TransferText acImportDelim, "yourimportspec", "Tablogs", filename, True

 

[jerry28ph]

hi kiwiman,

thanks a lot for your guidance. I got it now.
Thanks a million.

Regards,
Jerry

Howzit

To create an import spec, import a file manually.

• Rt Click anywhere in the object page white space and select import
• Navigate to file location and select file and correct file type
• Select the delimited format and then next
• Choose the delimiter type
• Select First Row contains field names (if the row headings are consistent for all your files
• Click Advanced
• Scroll down the field names checking the Data Type
• When you are happy with all the settings save the specification, make a note of the name so you can use in your import routine
• Test the import by importing into a staging table to ensure it works

Your final code for the transfer part will look like the below...

DoCmd.TransferText acImportDelim, "yourimportspec", "Tablogs", filename, True

 

[Kiwiman]

Howzit

Glad you worked it out. Sorry driving from Spain to the Uk over the last 4 days (at a nice leisurely pace).

Cheers