CSV Import Issue

[Tor_Fey]

Good Afternoon All;

I have the following code in a module in my database:

Function DoImport()

Dim strPathFile As String
Dim strFile As String
Dim strPath As String
Dim strTable As String
Dim blnHasFieldNames As Boolean

' Change this next line to True if the first row in CSV worksheet
' has field names
blnHasFieldNames = True

' Replace C:\Documents\ with the real path to the folder that
' contains the CSV files
strPath = "C:\ImportData"

' Replace tablename with the real name of the table into which
' the data are to be imported

strFile = Dir(strPath & "*.csv")


Do While Len(strFile) > 0
       strTable = Left(strFile, Len(strFile) - 4)
       strPathFile = strPath & strFile
       DoCmd.TransferText acImportDelim, , strTable, strPathFile, blnHasFieldNames


' Uncomment out the next code step if you want to delete the
' EXCEL file after it's been imported
'       Kill strPathFile

       strFile = Dir()

Loop


End Function

This code works really well for importing data and creating 600 plus tables, however the problem is.... it imports fields that are blank. Is there a way to exclude data if the first field has no data within it?

So an example of this would be as follows:

Its worth pointing out that.. the 600 plus CSV files are new data given by a third party, and I do not know ID field names. This is why I would like to know if I can exclude data if I don't know the field names.

Thanks in advance
Tor Fey

 

[Gasman]

My first point of call would be to ask them NOT to supply empty data fields? That to me is a cockup at their end, makes no sense at the moment?
When they say NO, then edit each file before you run your code, to tidy up the file with some VBA.

Otherwise I believe you will need to choose another method and not TransferText.

 

[theDBguy]

If all else fails, you can add a line in your module to delete the empty rows from your tables after each import with a DELETE query.

 

[jdraw]

I agree with TDG. But you might consider a "landing table for adjustments" where you import all data. Then, based on your requirements, you have a series of queries to:
-delete blank rows
-modify values based on some table/criteria
OR
Remove unneeded records at/in the source files before importing.

 

[Tor_Fey]

My first point of call would be to ask them NOT to supply empty data fields? That to me is a cockup at their end, makes no sense at the moment?
When they say NO, then edit each file before you run your code, to tidy up the file with some VBA.

Otherwise I believe you will need to choose another method and not TransferText.

Hi Gasman;

The supplier already said no that's why I am trying to do it with VBA or something similar, it's over 600 tables and don't want to write a delete query for each table.

Cheers
Tor Fey

 

[CJ_London]

you can either run a delete query after your transfertext or use a maketable query with criteria to exclude blank lines. But either depends on knowing your field names.

The basic maketable query would be

sqlstr = "SELECT * INTO " & strTable & _
    " FROM (SELECT * FROM [TEXT;DATABASE=" & strPath & ";HDR=Yes]." & strfile & ")  AS txt " & _
    " WHERE nz(AccountID,0)<>0;"
currentdb.execute sqlstr

but does rely on AccountID being common to all files

If the blank line can be identified by the first column being blank then you can execute a simple recordset to get the field name

fldname=currentdb.openrecordset(SELECT * FROM [TEXT;DATABASE=" & strPath & ";HDR=Yes]." & strfile & ") WHERE False).fields(0).name
sqlstr = "SELECT * INTO " & strTable & _
    " FROM (SELECT * FROM [TEXT;DATABASE=" & strPath & ";HDR=Yes]." & strfile & ")  AS txt " & _
    " WHERE '' +  " & fldname & " <>''"
currentdb.execute sqlstr

This is aircode, so the WHERE element may not be quite right but what it is supposed to be doing is converting all values, including nulls to strings

 

[theDBguy]

it's over 600 tables and don't want to write a delete query for each table.

You only need to write one delete query.

 

[jdraw]

We really have no idea of the scope of your issue/set up.
How different are the csv files?
Is this a recurring process?
How many of the csv have/do not have Fieldnames?

Could be as simple as ( concept not tested) after you sort out which files have/do not have fieldnames.

For each CSVwithFldNames in currentdb.tabledefs
  Import
  Run RemoveBlankRecords(tablename)
  Write someLogRecord
next

Just saw DBG and CJ's responses.

 

[Gasman]

Hi Gasman;

The supplier already said no that's why I am trying to do it with VBA or something similar, it's over 600 tables and don't want to write a delete query for each table.

Cheers
Tor Fey

I would expect it would be just one query run 600 times, but I agree not great, but the workarounds are just as bad.