Importing .csv

[radon]

I'm fairly certain this should be an easy one for the forum. I would like to know the code I should use to import several *.csv files into one temporary table. I suppose a simple import code would work. Thanks.

 

[georgedwilkinson]

Take a look at the TransferSpreadsheet or TransferText method in help.

 

[radon]

I still do not know how to structure the code to import a .csv file. So far I have,

DoCmd.TransferText acImportDelim, "", "tblTmpLoadXls", "C:\Users\UserName\Desktop\CSV Converts\", False

Obviously the actual code doesn't have "UserName" but you get the idea. Why doesn't this work and what do I need to change to make it work. The .csv files I'm trying to timport are all three columns wide and vary in length. They also don't have any headers.

 

[georgedwilkinson]

It looks on the surface like it doesn't work because you didn't include a file name. You'll need to call it 3 times, each time with the different file names.

Also, I take it you already have a table with the correctly formatted columns to accept this data (just to make sure).

If that's not it, you'll need to be more specific about how it doesn't work.

 

[radon]

Thanks for the quick reply. I don't understand what you mean by "call it 3 times". Does this mean that I need to have a specific name for the Specification Name? I will look into the table where I'm sending the .csv files, it was previously set up for excel files and I'm not sure if I need to change anything in that table. The old excel files that did go in that table where converted to .csv and those are the ones I'm working with now. It was suggested to me that I use .csv and not excel files to use with Access. I had errors with importing the excel files and I was led to believe that .csv files would be easier to work with in Access than the excel ones.

 

[georgedwilkinson]

You need to call TransferText with the full file name. Since you said you have several files that you need to transfer, that means you'd have to call it several times, once each for each of the files you need to transfer. Somehow my old brain added the "3" when you said several.

I disagree that .csv is a better format. What happens when the incoming data has a comma in it?

 

[radon]

Well what would I do if I had 240 .csv files that need importing? I would have to agree that several would imply 3 but I have rainfall data for the past 5 years at six locations.

None of the files have commas in them but I could see how that would pose a problem.

 

[georgedwilkinson]

You can just iterate through all the files using the Dir() function and make repeated calls to TransferText using the file name from Dir.

 

[radon]

I'm borrowing from the previous excel import code but is this what you mean?

DoCmd.TransferText acImportDelim, "", "tblTmpLoadXls", "C:\Users\UserName\Desktop\CSV Converts\", False, txt_box_file_path & strFileName strFileName = Dir()

I hate to keep asking the simple but as I'm sure you've guessed I'm not as aquanted with the program as I should be.

 

[georgedwilkinson]

Try this:

Sub TransferAll(p_strDirectory As String, Optional p_strFile As String = "*.*")
Dim l_strFile As String
    If Right(Trim(p_strDirectory), 1) <> "\" Then
        p_strDirectory = Trim(p_strDirectory) & "\"
    Else
        p_strDirectory = Trim(p_strDirectory)
    End If
    l_strFile = Dir(p_strDirectory & p_strFile)
    Do While l_strFile <> ""
        DoCmd.TransferText acImportDelim, "", "tblTmpLoadXls", l_strFile, False
        DoEvents
        l_strFile = Dir()
        DoEvents
    Loop
End Sub

I tested all of it except the "TransferText" line. Put it in a module.

To use it:
TransferAll ("C:\Users\UserName\Desktop\CSV Converts\", "*.csv")

 

[ajetrumpet]

George,

my question for you is...why does Access try to name the Access fields in the table as "F1", "F2", etc... when you try to import files like these into an existing table?????

 

[radon]

I will take me a while to digest what all that code is doing. I honestly thought this whole mess would be solved in one line. Could you quickly run down what exactly that code is doing in layman terms?

 

[georgedwilkinson]

my question for you is...why does Access try to name the Access fields in the table as "F1", "F2", etc... when you try to import files like these into an existing table?????

Dunno, I've never seen that happen. Send me a sample.

radon, I wrote that subroutine to do all the thinking for you based on my understanding of your need. Not all of it is required to do what you want done.

The magic occurs in the do loop.

To "prime" the Dir() command, you have to run it once before you can iterate with it.

So, I've included comments in the code to explain what it is doing.

Sub TransferAll(p_strDirectory As String, Optional p_strFile As String = "*.*") 'Pass in the directory you want to iterate through here. I used the default parameter of "*.*" so I could test on my system which has no .csv files.
Dim l_strFile As String 'A place to hold the results of the Dir command.
    
    If Right(Trim(p_strDirectory), 1) <> "\" Then
        'I just want to make sure that we build a file name string that is properly formed.
        p_strDirectory = Trim(p_strDirectory) & "\" 'Used to dummy proof.
    Else
        p_strDirectory = Trim(p_strDirectory)
    End If
    
    l_strFile = Dir(p_strDirectory & p_strFile) 'Run the Dir() function to "prime" the Dir pipeline and get the first file name.
    
    Do While l_strFile <> "" 'If the most recent call to Dir returns no result, exit the loop.
        'Your code, I don't really know if it is written correctly
        DoCmd.TransferText acImportDelim, "", "tblTmpLoadXls", l_strFile, False
        
        DoEvents 'Just me being cautious in case you get into an infinite loop
        
        l_strFile = Dir() 'For subsequent calls to Dir after the first "priming" call.  See the help file for Dir.
        
        DoEvents 'Again, just me being cautious in case you get into an infinite loop
    Loop 'Try the loop again
    'The loop is over and processing is complete.
End Sub

 

[ajetrumpet]

george,

do u have vista or xp?

 

[georgedwilkinson]

Vista at home (with a 2000 machine if needed). XP at work.

 

[ajetrumpet]

i can't send it to you george, because I'm having the problem here.

 

[radon]

Thanks for your help thus far, sorry I'm being thick. I noticed that I had a *.xlsx still the in the code I was using. After changing that to a *.csv I got an error, "Invalid Argument". I figured I'd pass the code on to you and see what you think.

Private Sub Cmd_Upload_Data_Click()
On Error GoTo Err_Cmd_Upload_Data_Click
' Switch off all the WARNING msgs.
    DoCmd.SetWarnings True
Dim strFileName As String
Dim cstrPath As String
DoCmd.OpenQuery "Query_del_temp_load_tab"
MsgBox "Temp Table Deleted"
cstrPath = txt_box_file_path.Value
'MsgBox " File Path " & cstrPath
strFileName = Dir(cstrPath & "*.csv")
MsgBox "Excel Name" & strFileName
Do While strFileName <> ""
    DoCmd.TransferText acImportDelim, "", "tblTmpLoadXls", "C:\Users\gmassengale\Desktop\CSV Converts\", False, txt_box_file_path & strFileName = Dir()
Loop
MsgBox "Data upload Successful."
' Switch on the WARNING msgs.
    DoCmd.SetWarnings True
Exit_Cmd_Upload_Data_Click:
    Exit Sub
Err_Cmd_Upload_Data_Click:
    MsgBox Err.Description
    Resume Exit_Cmd_Upload_Data_Click
End Sub

 

[gemma-the-husky]

adam

isnt the F1/F2 headers something to do with whether you specify that the csv has field names in the first column

if you say not, and dont use an import spec, access will allocate F1, F2 etc as field names in the table

i dont understand how they would get used if you imported to an existing table though.

 

[ajetrumpet]

adam

isnt the F1/F2 headers something to do with whether you specify that the csv has field names in the first column

if you say not, and dont use an import spec, access will allocate F1, F2 etc as field names in the table

i dont understand how they would get used if you imported to an existing table though.

I'm not sure either Gemma. That's why I think corruption is starting to creep in. The error message that I am getting doesn't make any sense. Not sure what to do...help??

 

[radon]

Does anyone know why I get the 'Invalid Argument' error when I run the code that I last posted?