Access VBA - Deleting a line in a CSV (1 Viewer)

Brad VTP

New member
Local time
Today, 08:19
Joined
Sep 18, 2020
Messages
2
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

Lifelong Learner
Local time
Today, 06:19
Joined
Mar 14, 2017
Messages
8,738
This should work. Aircode, might need a minor adjustment or have typo.

Code:
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.
 
Last edited:

namliam

The Mailman - AWF VIP
Local time
Today, 14:19
Joined
Aug 11, 2003
Messages
11,696
Simply import the file into a temp table, then append all records excluding the ones you dont want into a permenant table
 

Brad VTP

New member
Local time
Today, 08:19
Joined
Sep 18, 2020
Messages
2
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

Lifelong Learner
Local time
Today, 06:19
Joined
Mar 14, 2017
Messages
8,738
Sounds good, let us know how you get on.
PS .. maybe my code should have been:

Code:
Sub Something(strOldPath as string, strNewPath as string)
.... 'in previously provided code, refer to strOldPath and strNewPath
End Sub
or better yet
Code:
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:
Code:
If Something=True then
   ...continue original import routine
else
   ...proceed somehow else, knowing that it failed/erred
End if
 

Pat Hartman

Super Moderator
Staff member
Local time
Today, 09:19
Joined
Feb 19, 2002
Messages
42,981
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

Lifelong Learner
Local time
Today, 06:19
Joined
Mar 14, 2017
Messages
8,738
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

Super Moderator
Staff member
Local time
Today, 13:19
Joined
Feb 19, 2013
Messages
16,555
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
Code:
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
 
Last edited:

Isaac

Lifelong Learner
Local time
Today, 06:19
Joined
Mar 14, 2017
Messages
8,738
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
Code:
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
 

Users who are viewing this thread

Top Bottom