Access VBA - Deleting a line in a CSV

[Brad VTP]

We are trying to process a CSV file into an Access table where the first line contains an instruction code "sep=^" (quotation marks only shown here to distinguish the text found in the first line; they are not contained in the file) that presumably indicates that the separator between fields is a carrot (^). If it is left in, the rest of the import routine does not process correctly. If this line is removed, the first line of the CSV file is a typical field name header row, which the routine is looking for.

Currently, we have to manually open the CSV in Notepad (or similar), delete the first line, and then resave the file before running our Access VBA import routine. We would like to eliminate this manual process and incorporate it directly into our VBA import processing routines. OR, if possible, to be able to skip over this line and start processing with the second line being treated as if it were the first line of the file containing the "header row" information. But, because we are not sure that ALL files will contain this first row separator instruction, an evaluation must be performed.

We are looking for how to add in a subroutine to evaluate the CSV file after opening it to determine if this first line contains "sep=^" ( again, the text in the file does not include the quotation marks). This will be the first text in the first line of the file, if it exists.

But, if the file does not contain this "delimiter specification" line (for example, the file has already been manually edited), we want to skip the process to delete the line (since that step is no longer required) and continue to the data import processing.

So,
if "sep=^" exists, we want to delete the entire line (row) in the file.
If not, proceed with the rest of the import routine.

This doesn't seem that this should be so difficult, but we are at a loss as to how to build this into the process. In searches, I have not found anything that references this type of situation with processing a CSV or text file. Looking for some assistance. Thanks!

 

[Isaac]

This should work. Aircode, might need a minor adjustment or have typo.

dim fso as object, tsOld as object, tsNew as object, strPathOld as string, strPathNew as string
dim strLine as string
set fso=createobject("scripting.filesystemobject")

strPathOld="enter the path to the existing textfile here"

set tsOld = fso.opentextfile(strPath,1)
set tsNew = fso.createtextfile("enter path to the new file here")

do until tsOld.atendofstream=true
    strLine=tsOld.readline
    if instr(1,strLine,"sep=^")=0 then
        tsNew.writeline strLine
    end if
loop
tsOld.close
tsNew.close

Edit - I recommend testing which is faster & less overhead, this ... (manipulating text file), or importing into a table and then running an append query.
If namliam method is faster, go for it. Also consider database bloat from the table process.
If it were me, for more complex or "multiple" manipulations needed to the input data, I'd probably use a table driven process to better get my arms around the possibilities. For a very simple, or single, manipulation, it might be less costly to your application to manipulate the file first. IMHO.

 

[namliam]

Simply import the file into a temp table, then append all records excluding the ones you dont want into a permenant table

 

[Brad VTP]

Thanks, Isaac and namliam!

Yes, we are going to try Isaac's routine. I completely agree, that the testing process will be more efficient. Currently, after selecting the file, the data processes into a temporary table so it can be given a "once over" QC review. We have a second routine built to move the data into the primary data table after we complete that review and are okay with the data (occasionally there may be something we need to manually adjust, but mostly just to verify that the data processed as expected). The initial routine empties the temp table before we process the next file which controls the "overhead."

As noted, We have designed a relatively simple two step process and are including the import into a temp file as namliam has suggested as a key component of the first step. But, if we leave that "delimiter specification" line in the file - or (more likely in ongoing activity) If we forgot to remove the line from the CSV - our VBA draws an error and some of the processing steps that follow to reformat certain fields and add others as the data is brought into the temp table, do not occur. So, unfortunately, that route entails a lot more work in updating fields (or a lot more coding to create the work around to address what to do when the error is encountered).

Thanks! Will let you know how we fair.

 

[Isaac]

Sounds good, let us know how you get on.
PS .. maybe my code should have been:

Sub Something(strOldPath as string, strNewPath as string)
.... 'in previously provided code, refer to strOldPath and strNewPath
End Sub

or better yet

Function Something(strOldPath as string, strNewPath as string) as Boolean
On Error goto errhandler

.... 'in previously provided code, refer to strOldPath and strNewPath

Something = True 'no error occurred

exit function
errhandler:
something=false
exit function

End Function

And then called using some routine at which point you've decided what the paths are/will be so you can just call it and be done.
Or call the function and test if it succeeded:

If Something=True then
   ...continue original import routine
else
   ...proceed somehow else, knowing that it failed/erred
End if

 

[Pat Hartman]

To use FSO (File System Object), you need to add a reference using Tools/References from the VBA window.
I think the library is Microsoft Office xx.x Object Library
But it might be Microsoft Scripting Runtime

I use both for different reasons and I'm having a Senior moment.

 

[Isaac]

To use FSO (File System Object), you need to add a reference using Tools/References from the VBA window.
I think the library is Microsoft Office xx.x Object Library
But it might be Microsoft Scripting Runtime

I use both for different reasons and I'm having a Senior moment.

It's the latter one you mentioned - but I used late binding, so the reference s/b unnecessary.

 

[CJ_London]

another way is to use sql - something like

SELECT *
FROM (SELECT * FROM [TEXT;DATABASE=C:\folderpath;HDR=no].filename.csv) AS txt;

this will open the text file with all columns identified as F1, F2, F3 etc and will display sep=^ as a value in the first row of F1 with the headers in the second row

you can eliminate the first row with criteria

SELECT *
FROM (SELECT * FROM [TEXT;DATABASE=C:\folderpath;HDR=no].filename.csv WHERE F1<>'sep=^') AS txt;

or that and the header row to leave just the data

SELECT *
FROM (SELECT * FROM [TEXT;DATABASE=C:\folderpath;HDR=no].filename.csv WHERE F1 not in ('sep=^','F1Header')) AS txt;

substitute the name of your first column heading for F1Header

if you want the headers rather than F1 etc

SELECT *
FROM (SELECT F1 as header1, F2 as Header2 etc FROM [TEXT;DATABASE=C:\folderpath;HDR=no].filename.csv WHERE F1 not in ('sep=^','F1Header')) AS txt;

which may or may not matter but gives you the opportunity to rename columns into something more relevant to your app. Or you can use vba to run a variation to just return the header row which you can then use to build the above query

dim rst as dao.recordset
dim sqlstr as string
dim fieldList as string
dim i as integer
set rst=currentdb.openrecordset("SELECT TOP 1 * FROM [TEXT;DATABASE=C:\folderpath;HDR=no].filename.csv WHERE F1<>'sep=^'")


fieldlist=""
for i=1 to rst.fields.count
    fieldlist= fieldlist & ", F" & i & " AS " rst("F"& i) 
next i
sqlstr="SELECT * FROM (SELECT " & mid(fieldlist,3) & " FROM [TEXT;DATABASE=C:\folderpath;HDR=no].filename.csv  WHERE F1 not in ('sep=^','F1Header')"

this basic query can be joined to existing tables, changed to an insert, update, etc query and of course you can apply additional criteria to limit the number of records returned (subject to performance against a large dataset with no indexing)

I alias the query, just to make it easier to use

I use this method all the time for data validation before importing to my app

 

[Isaac]

another way is to use sql - something like

SELECT *
FROM (SELECT * FROM [TEXT;DATABASE=C:\folderpath;HDR=no].filename.csv) AS txt;

this will open the text file with all columns identified as F1, F2, F3 etc and will display sep=^ as a value in the first row of F1 with the headers in the second row

you can eliminate the first row with criteria

SELECT *
FROM (SELECT * FROM [TEXT;DATABASE=C:\folderpath;HDR=no].filename.csv WHERE F1<>'sep=^') AS txt;

or that and the header row to leave just the data

SELECT *
FROM (SELECT * FROM [TEXT;DATABASE=C:\folderpath;HDR=no].filename.csv WHERE F1 not in ('sep=^','F1Header')) AS txt;

substitute the name of your first column heading for F1Header

if you want the headers rather than F1 etc

SELECT *
FROM (SELECT F1 as header1, F2 as Header2 etc FROM [TEXT;DATABASE=C:\folderpath;HDR=no].filename.csv WHERE F1 not in ('sep=^','F1Header')) AS txt;

which may or may not matter but gives you the opportunity to rename columns into something more relevant to your app. Or you can use vba to run a variation to just return the header row which you can then use to build the above query

dim rst as dao.recordset
dim sqlstr as string
dim fieldList as string
dim i as integer
set rst=currentdb.openrecordset("SELECT TOP 1 * FROM [TEXT;DATABASE=C:\folderpath;HDR=no].filename.csv WHERE F1<>'sep=^'")


fieldlist=""
for i=1 to rst.fields.count
    fieldlist=", F" & i & " AS " rst("F"& i) & fieldlist
next i
sqlstr="SELECT * FROM (SELECT " & mid(fieldlist,3) & " FROM [TEXT;DATABASE=C:\folderpath;HDR=no].filename.csv  WHERE F1 not in ('sep=^','F1Header')"

this basic query can be joined to existing tables, changed to an insert, update, etc query and of course you can apply additional criteria to limit the number of records returned (subject to performance against a large dataset with no indexing)

I alias the query, just to make it easier to use

I use this method all the time for data validation before importing to my app

Very cool

 

[CJ_London]

I use a similar techniq