Importing .csv

[georgedwilkinson]

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

Just a guess. You primed Dir() and then went on to ignore the first result from Dir(). That's not the way a Do While loop works.

You need to use the value from that first run of Dir. Don't use Dir() directly in your transfer method. Assign Dir() to a variable as one of the last things you do within a loop.

 

[radon]

Don't I already have a l_strFile typr parameter in the code with the cstrPath as the directory where the files are that I want to import. That is an input by the user, couldn't I call on that instead of making a new one within the code? or am I missing the point completely? I made these changes but got the same error. Your timely responses are awesome by the way, thanks.

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
Dim l_strFile 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", cstrPath, False
Loop
cstrPath = Dir()
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

 

[georgedwilkinson]

Move your final Dir() to just inside your loop.

 

[radon]

When I do that the msg box that is supposed to have 'strFileName' which is supposed to represent Dir(cstrPath & ".csv") comes up blank. The tempLoad table is also still blank after the code runs, although I don't have any errors now.

 

[radon]

I fixed the problem with the code below but now it says that Access cannot find the first file that it needs to import.

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
Dim l_strFile 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 "CSV Name " & strFileName
Do While strFileName <> ""
    DoCmd.TransferText acImportDelim, "", "tblTmpLoadXls", strFileName, False
    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

 

[georgedwilkinson]

Try this:

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
Dim l_strFile 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 "CSV Name " & strFileName
Do While strFileName <> ""
    DoCmd.TransferText acImportDelim, "", "tblTmpLoadXls", [B][COLOR=red]cstrPath &[/COLOR][/B] strFileName, False
    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

 

[radon]

With that variation I get no errors and an empty table. I also have a semi blank msg box with that too, the box that is supposed to display 'MsgBox "Csv Name " & strFileName'.

In the original code the do cmd was

DoCmd.TransferSpreadSheet acImport, acSpreadsheetTypeExcel9, "tblTempLoadXls",txt_box_file_path & strFileName

if that helps.

 

[georgedwilkinson]

I'm guessing there are no files in the directory that meet the criteria.

If you're sure there are files there, do this:
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
Dim l_strFile As String
DoCmd.OpenQuery "Query_del_temp_load_tab"
MsgBox "Temp Table Deleted"
cstrPath = txt_box_file_path.Value
Debug.Print cstrPath
'MsgBox " File Path " & cstrPath
strFileName = Dir(cstrPath & "*.csv")
Debug.Print strFileName
Debug.Print cstrPath & strFileName 'I think this might tell you a lot about what's wrong.
MsgBox "CSV Name " & strFileName
Do While strFileName <> ""
DoCmd.TransferText acImportDelim, "", "tblTmpLoadXls", cstrPath & strFileName, False
strFileName = Dir()
Debug.Print strFileName 'This will probably tell you the rest of what's wrong.
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

 

[radon]

Really dumb question, where is it supposed to print? I put the extra lines of code in but there was no change in what happened.

Also, how can there be no files that me the criteria. From what I see, and I guess I'm wrong, all the files have to be are in the directory that I specified in the text box and they have to end in .csv. I am certain of the file path I provided in the text box and of the fact that they end in .csv, as they all do.

 

[radon]

Wow I just put my foot in my mouth. The file path was incorrect *sigh*. Though this led to a new problem. It loaded some of the files, not nearly all of them and the ones it did add it added twice. The error that stopped the import process said there was no F4 field. There shouldn't be a fourth field in any of the files.

 

[georgedwilkinson]

That's a common error that has been hashed out many times on these forums.

 

[radon]

I suppose its a common error that been hashed out several places. My problem now is with the data once it is imported. I moved from excel files to .csv's to avoid blank spaces in my table and other unwanted small things. All I wanted was a list with no gaps of all of my data. Why would the table have blank gaps and why would it import files twice or not import all of them in one run through?

I realize that without the specific files I'm refering to there may be little you can do. I am also sorry for running you around and around just because I can't get the little stuff right but thanks for the help you've given, it's been a huge help.

 

[gemma-the-husky]

have you got this sorted yet

what i would do in cases like this is try to modularise the code, to avoid having a single monolithic block of code, which becomes very difficult to analyse

so - have one procedure for the directory read loop
have another procedure for the import etc

easier to follow, and to debug, and to maintain/improve

so you get in pseudocode

sub mainloop
readfirstfile
if filexexists then
    while not nomorefiles
       processfile  'see below
       readnextfile
    wend
end
end sub


sub processfile
'whatever you want
end sub

so with this logic, while you are debugging, you can just display file names, in the process loop, to make sure your directory read works ok.

you could then do other things, such as make the dir read loop more sophisticated , by scanning subdirectories if you wanted, recursively

after you are happy with that bit, then you can build in processing code

 

[radon]

New question, same topic. I've got the same data set I've been working with but now they're in .xslx. I heard it through the grapevine that Access doesn't like the date/time format to be "mm/dd/yy hh:mm:ss.0" with the .0 portion denoting half second marks. The archaic program we use in the field collects data in this manner. Anyway, I am still trying to import this data like before but even after ridding the data set of the half second crud I still get half imports of files and the recurring problem of a ghost F4 field. Here's the code:

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
Debug.Print cstrPath
'MsgBox " File Path " & cstrPath
strFileName = Dir(cstrPath & "*.xlsx")
Debug.Print strFileName
Debug.Print cstrPath & strFileName
MsgBox "CSV Name " & strFileName
Do While strFileName <> ""
    DoCmd.TransferSpreadsheet acImport, acSpreadsheetTypeExcel12, "tblTempLoadXls", txt_box_file_path & strFileName
    strFileName = Dir()
Debug.Print strFileName
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

The data is formatted like this:
F1.........................................F2..................F3
mm/dd/yy hh:mm:ss sequential numbering Location

Any idea about why some files would import correctly and others only partially? Or why some seem to have a ghost F4 field?

 

[datAdrenaline]

May I ask the structure of your destination table? ... ie: What are the field names and types. The reason I ask is that I prefer to import text data with an SQL Statement (see here). I find that using the SQL statement gives a bit of flexibility that I really like to take advantage of.

 

[radon]

F1 date/time (general date)
F2 text
F3 text

I hope that's what you wanted.

 

[datAdrenaline]

Those are the field names of your destination table (not your CSV) correct? ... so .. your CSV, I'm guessing, does NOT have a header row? ... Also, once the data is in your Access Table, do you do subsequently populate another table? ... Also, I notice that you are now import an Excel file ... so ... are you still trying to get CSV into the database, or are you going to use Excel as the source.

 

[datAdrenaline]

Hello Radon ...

Here is my go at it ... with the ASSUMPTION you do save off your data in CSV format ...

Private Sub Cmd_Upload_Data_Click()
    
    Dim strFileName As String
    Dim cstrPath As String
    Dim db As DAO.Database
    Dim strSQL As String
    
    'Initialize
    cstrPath = Me.txt_box_file_path.Value
    Set db = CurrentDb
    
    'Clean the temp table
    strSQL = "DELETE FROM tblTempLoadXls"
    db.Execute strSQL, dbFailOnError
    
    'Loop through all the CSV's
    strFileName = Dir(cstrPath & "*.csv")
    Do While strFileName <> ""
        
        'Build the SQL, note that I assumed your cstrPath HAD a trailing (\)
        'which I remove when building the SQL.
        strSQL = "INSERT INTO tblTempLoadXls (F1, F2, F3)" & _
             " SELECT F1, F2, F3" & _
             " FROM  [Text" & _
                    ";FMT=Delimited;HDR=NO;CharacterSet=437" & _
                    ";DATABASE=" & Left(cstrPath, Len(cstrPath) - 1) & "]." & _
                    "[" & strFileName & "];"
        
        'Execute the SQL statement
        db.Execute strSQL, dbFailOnError
        
        'Get the next file name
        strFileName = Dir()
        
    Loop
    
    'Provide feedback
    MsgBox "Data upload Successful."
    
End Sub

 

[datAdrenaline]

Just a point of note .... with the method I have proposed, you can "scrub" the date field to be a valid date with in the SELECT statement ... something like:

IIf(Len(F1), CDate(Left(F1, InStr(1,F1,".") - 1)), Null)

 

[radon]

What would the code look like if I used excel files?