Fixing a csv File Before Importing Data (1 Viewer)

[scotthutchings]

I have a client that uses a software application that exports the data to a csv. The problem is that the file places the first line of data in the first row that also contains the column names. Here is an example:

"Product Summary","200009 : SCA La Veta ASC","Mfg","Product Description","List Price","Unit Cost","Sell Price","Qty","List Price","Extended","Extended","Cost","Extended","Sell Price","No Products found for selected project","IV","Hinges 5BB1 4 1/2 x 4 1/2 HT 626","36.70","13.76","20.95","144","5284.80","1981.44","3016.80","Openings: 101(3), 105(3), 106(3), 106A(3), 106B(3), 106C(3), 107(3), 107A(3), 107B(3), 107D(3), 107E(3), 108(3), 109(6), 110(3), 112(3), 113(3), 114(3), 115(3), 116(3), 121(3), 122(3), 125(3), 131(3), 132(3), 145(3), 145A(3), 146(3), 146A(3), 148(3), 149(3), 150(3), 152(3), 153(3), 154(3), 155(3), 155A(3), 155B(3), 156(3), 158(3), 159(3), 160(3), 161(6), 162(3), 163(3), 164(3), 165(3)","September 17, 2010 3:01 pm","Page -1 of 1"
"IV","Hinges 5BB1SC 4 1/2 HT 652","56.80","21.30","32.43","6","340.80","127.80","194.58","Openings: 129A(6)","September 17, 2010 3:01 pm","Page -1 of 1"
"IV","Set Auto Flush Bolts FB51P US32D","151.20","56.70","86.33","2","302.40","113.40","172.66","Openings: 109, 161","September 17, 2010 3:01 pm","Page -1 of 1"
"SC","Lockset ND50PD SPA 626","428.00","160.50","244.36","7","2996.00","1123.50","1710.52","Openings: 105, 112, 114, 115, 116, 160, 164","September 17, 2010 3:01 pm","Page -1 of 1"

In every file, the last column name is "No Products found for selected project". The first line of data, then begins after the next comma. How can I import the first line of data? Here is my current code:

DoCmd.TransferText acImportDelim, ImportSpec, "Project Material", strFileName, yes

Is there a way that I can have my Access application modify the text file before importing it or is there another way to import it that might work better?

Thanks for your help.

Scott

 

[boblarson]

Does this part always exist?

SCA La Veta ASC"

before the headings start?

 

[scotthutchings]

In structure but not in content. This is the project ID and the Project Name. Thes data will always be different but they will always be in the file.

 

[boblarson]

Well, see if this works for you.

Function CleanTextFile(strFilePath As String)
    Dim objXL As Object
    Dim objWB As Object
 
    Set objXL = CreateObject("Excel.Application")
 
    Set objWB = objXL.Workbooks.Open(strFilePath)
 
    objWB.ActiveSheet.Range("A1:B1").Select
    objXL.Selection.Delete Shift:=-4159
 
    objXL.DisplayAlerts = False
 
    objWB.Save
    objWB.Close

    objXL.DisplayAlerts = True
 
    objXL.Quit
 
    Set objWB = Nothing
    Set objXL = Nothing

End Function

 

[scotthutchings]

This removed the leading data (the Project ID, Project Name, etc) but the first row of data still falls after the column headings and does not show up in the transfer text operation.

Also, it removed many of the double quotes. Is this going to be a problem?

Do I need to move the data to the second line?

(Here is what the csv looks like after running the code)

Mfg,Product Description,List Price,Unit Cost,Sell Price,Qty,List Price,Extended,Extended,Cost,Extended,Sell Price,No Products found for selected project,IV,Hinges 5BB1 4 1/2 x 4 1/2 HT 626,36.7,13.76,20.95,144,5284.8,1981.44,3016.8,"Openings: 101(3), 105(3), 106(3), 106A(3), 106B(3), 106C(3), 107(3), 107A(3), 107B(3), 107D(3), 107E(3), 108(3), 109(6), 110(3), 112(3), 113(3), 114(3), 115(3), 116(3), 121(3), 122(3), 125(3), 131(3), 132(3), 145(3), 145A(3), 146(3), 146A(3), 148(3), 149(3), 150(3), 152(3), 153(3), 154(3), 155(3), 155A(3), 155B(3), 156(3), 158(3), 159(3), 160(3), 161(6), 162(3), 163(3), 164(3), 165(3)",9/17/2010 14:27,Page -1 of 1
IV,Hinges 5BB1SC 4 1/2 HT 652,56.8,21.3,32.43,6,340.8,127.8,194.58,Openings: 129A(6),9/17/2010 14:27,Page -1 of 1,,,,,,,,,,,,,
IV,Set Auto Flush Bolts FB51P US32D,151.2,56.7,86.33,2,302.4,113.4,172.66,"Openings: 109, 161",9/17/2010 14:27,Page -1 of 1,,,,,,,,,,,,,
SC,Lockset ND50PD SPA 626,428,160.5,244.36,7,2996,1123.5,1710.52,"Openings: 105, 112, 114, 115, 116, 160, 164",9/17/2010 14:27,Page -1 of 1,,,,,,,,,,,,,

 

[boblarson]

Okay, so what is the last header name?

 

[scotthutchings]

"No Products found for selected project"

 

[boblarson]

Okay, here you go (there may be a more efficient way but this is what I wound up with).

Function CleanTextFile(strFilePath As String)

    Dim objXL As Object
    Dim objWB As Object
    Dim strStartAddress As String
    Dim strEndAddress As String
 
    Set objXL = CreateObject("Excel.Application")
    Set objWB = objXL.Workbooks.Open(strFilePath)
 
    With objXL

        objWB.ActiveSheet.Range("A1:B1").Select

        .Selection.Delete Shift:=-4159
        .Rows("2:2").Select
        .Selection.Insert Shift:=xlDown
 
        objWB.ActiveSheet.Range("A1").Select
 
        Do Until .ActiveCell.Value = "No Products found for selected project"
            .ActiveCell.Offset(0, 1).Select
        Loop

        .ActiveCell.Offset(0, 1).Select

        strStartAddress = .ActiveCell.Address
 
        Do Until .ActiveCell.Value = ""
            .ActiveCell.Offset(0, 1).Select
        Loop

        .ActiveCell.Offset(0, -1).Select

        strEndAddress = objXL.ActiveCell.Address
 
        .ActiveSheet.Range(strStartAddress & ":" & strEndAddress).Select

        .Selection.Cut
        .ActiveSheet.Range("A2").Select
        .ActiveSheet.Paste

        .DisplayAlerts = False
 
        objWB.SaveAs objWB.FullName, 6
 
        objWB.Close

        .DisplayAlerts = True
 
        .Quit

    End With

    Set objWB = Nothing
    Set objXL = Nothing

End Function

 

[boblarson]

Actually, never mind. It isn't quite there yet.

 

[scotthutchings]

Thank you for your help. One thing I need to account for is whether or not the CleanTextFile has already run on the file. If I select a file that has already run, it encounters errors (maybe this is what you're working on). Also, I don't anticipate this, but what if the user doesn't have Excel?

 

[boblarson]

Nah, I'm trying to figure out the best way to output so you get your quotes but it isn't going well. And if a user doesn't have Excel then it should be coded a different way. The only way I can think of to do is a very tedious bit of coding which would take it from one CSV file and directly move it into another one. It is partially based on what I'm trying with this Excel code (you haven't see this yet) but it would be more difficult (as far as I can see).

 

[sonof27]

Forgive me if this is not correct, or not the correct way to do this, it is my first time posting. This should help you grab the text from your original csv, allow you to manipulate it, then save it back to a different csv, it works in access2000. Very messy though as I have borrowed bits and pieces from god knows where over time. The only bit of manipulation I have done here is to isolate the header line and add a line feed. You may need to add a little of your own manipulating. I have two functions, one for getting the data the other for saving the data and the manipulating code is behind a button.

Function sImportAll(strFileLoc)
    On Error GoTo E_Handle
    Dim strImport As String
    Dim lngChars As Long
    Dim intFile As Integer
    intFile = FreeFile
    Open strFileLoc For Input As intFile
    lngChars = LOF(intFile)
    sImportAll = Input(lngChars, intFile)
sExit:
    On Error Resume Next
    Reset
    Exit Function
E_Handle:
    MsgBox Err.Description, vbOKOnly + vbCritical, "Error: " & Err.Number
    Resume sExit
End Function
 
Function sExportAll(strFileLoc, strText)
    On Error GoTo E_Handle
    Dim strImport As String
    Dim lngChars As Long
    Dim intFile As Integer
    intFile = FreeFile
    Open strFileLoc For Output As #1
    Print #1, strText
sExit:
    On Error Resume Next
    Reset
    Exit Function
E_Handle:
    MsgBox Err.Description, vbOKOnly + vbCritical, "Error: " & Err.Number
    Resume sExit
End Function
 
Private Sub btnTestCode_Click()
Dim strFileData
Dim varFirst
Dim strLast
Dim varColumnCount
On Error GoTo ErrorHandler
' grab all the file data
crap = sImportAll("D:/test.csv") ' change to your fale name
' manipulate the file data
' find the first instance of the first column heading
varFirst = InStr(1, crap, "Mfg") ' change if first column heading is not "Mfg"
If varFirst > 0 Then
    crap = Mid(crap, varFirst - 1)
Else
    MsgBox "The expected first column does not exists." & vbCrLf & "This process has stopped."
    Exit Sub
End If
'find end of first row, that is the end of the header
strLast = "No Products found for selected project" ' change if last column heading is not this
varFirst = InStr(1, crap, strLast)
strFileData = Mid(crap, 1, varFirst + Len(strLast)) & vbCrLf
crap = Mid(crap, varFirst + Len(strLast) + 2)
' get the number of columns - not actually used here but may be needed as part of the manipulation
Dim colPos As Long
varColumnCount = 0
colPos = InStr(strFileData, ",")
Do Until colPos = 0
    varColumnCount = varColumnCount + 1
    colPos = InStr(colPos + 1, strFileData, ",")
Loop
' send all the data back to a different file
crap = strFileData & crap
crap = sExportAll("D:/test2.csv", crap) ' change to your fale name
MsgBox "Done"
Exit Sub
ErrorHandler:
MsgBox Err.Description, vbOKOnly + vbCritical, "Error: " & Err.Number
End Sub

 

[vbaInet]

I think what Bob is trying to achieve there is having not to copy ALL the contents into another Excel file.

Maybe the OP can post a sample file we can work with.

 

[boblarson]

SonOf27 has the basic idea where I was going. The big thing is that the original CSV file has quotes around everything, so if that needs to be preserved then the writing to the open text file will need to be done slightly different from SonOf27's example. But it is almost there from my viewing of the code. I will see if I can try it out later when I get to work (I'm on the bus on my way to work),

 

[sonof27]

vbaInet, if OP means original poster then it would be nice if the OP also posted what the data should look like at the end.

boblarson. I open the csv files with notepad and not excel, the user does not need excel to run this, and the quotes appear to remain within the second file.

It does not need to be a second file, it can be saved back to the original file name. It's just I tend to move the original file to a separate storage space for prosperity, and the secod file is always the same name so you import the same file every time. I hope that makes sense.

 

[namliam]

How about doing something simple like:

Sub mytest()
    Dim myString As String
    Open "c:\mytest.txt" For Input As #1
    Open "c:\myOutput.txt" For Output As #2
    Line Input #1, myString
    Print #2, Left(myString, 223)
    Print #2, Mid(myString, 225)
    Line Input #1, myString
    Do While Not EOF(1)
        Print #2, myString
        Line Input #1, myString
    Loop
    Print #2, myString
    Close #1
    Close #2
End Sub

This returns:

"Product Summary","200009 : SCA La Veta ASC","Mfg","Product Description","List Price","Unit Cost","Sell Price","Qty","List Price","Extended","Extended","Cost","Extended","Sell Price","No Products found for selected project"
"IV","Hinges 5BB1 4 1/2 x 4 1/2 HT 626","36.70","13.76","20.95","144","5284.80","1981.44","3016.80","Openings: 101(3), 105(3), 106(3), 106A(3), 106B(3), 106C(3), 107(3), 107A(3), 107B(3), 107D(3), 107E(3), 108(3), 109(6), 110(3), 112(3), 113(3), 114(3), 115(3), 116(3), 121(3), 122(3), 125(3), 131(3), 132(3), 145(3), 145A(3), 146(3), 146A(3), 148(3), 149(3), 150(3), 152(3), 153(3), 154(3), 155(3), 155A(3), 155B(3), 156(3), 158(3), 159(3), 160(3), 161(6), 162(3), 163(3), 164(3), 165(3)","September 17, 2010 3:01 pm","Page -1 of 1"
"IV","Hinges 5BB1SC 4 1/2 HT 652","56.80","21.30","32.43","6","340.80","127.80","194.58","Openings: 129A(6)","September 17, 2010 3:01 pm","Page -1 of 1"
"IV","Set Auto Flush Bolts FB51P US32D","151.20","56.70","86.33","2","302.40","113.40","172.66","Openings: 109, 161","September 17, 2010 3:01 pm","Page -1 of 1"
"SC","Lockset ND50PD SPA 626","428.00","160.50","244.36","7","2996.00","1123.50","1710.52","Openings: 105, 112, 114, 115, 116, 160, 164","September 17, 2010 3:01 pm","Page -1 of 1"

 

[vbaInet]

@sonof27: You're right, that's what OP means It could also mean Original Post. I haven't gone through your code but it seems it does the job.

@namliam: Much neater way. My only contention is the operation is longer due to reading from one to the other. I was just thinking of a more effecient way of doing it automating Notepad but it will involve sendkeys. So yes, you're right on the money.

 

[namliam]

Well in situations like this it is usually the best idea to go back to your source that is supplying the file and tell them it is corrupted and they need to fix it.

A alternative might be to import the file manually during the itteration of the file... Though I think this way is elegant enough if files do not get too big...

Another alternative is to use AWK/GREP commands but that dont exist in windows natively. Perhaps if you can install/(ab)use some third party simular software you can via a commandline insert the required CRLF

 

[vbaInet]

Yes, I would be inclined to go back to the source and get it sorted there.

Possibilities are that file is large. I suppose the OP would have to make do with what has been proposed.

I was just working on an Excel solution (which I think Bob had already done) and it worked quite quickly but the OP was looking for a non Excel solution.

 

[boblarson]

How about doing something simple like:

This returns:

"Product Summary","200009 : SCA La Veta ASC","Mfg","Product Description","List Price","Unit Cost","Sell Price","Qty","List Price","Extended","Extended","Cost","Extended","Sell Price","No Products found for selected project"
"IV","Hinges 5BB1 4 1/2 x 4 1/2 HT 626","36.70","13.76","20.95","144","5284.80","1981.44","3016.80","Openings: 101(3), 105(3), 106(3), 106A(3), 106B(3), 106C(3), 107(3), 107A(3), 107B(3), 107D(3), 107E(3), 108(3), 109(6), 110(3), 112(3), 113(3), 114(3), 115(3), 116(3), 121(3), 122(3), 125(3), 131(3), 132(3), 145(3), 145A(3), 146(3), 146A(3), 148(3), 149(3), 150(3), 152(3), 153(3), 154(3), 155(3), 155A(3), 155B(3), 156(3), 158(3), 159(3), 160(3), 161(6), 162(3), 163(3), 164(3), 165(3)","September 17, 2010 3:01 pm","Page -1 of 1"
"IV","Hinges 5BB1SC 4 1/2 HT 652","56.80","21.30","32.43","6","340.80","127.80","194.58","Openings: 129A(6)","September 17, 2010 3:01 pm","Page -1 of 1"
"IV","Set Auto Flush Bolts FB51P US32D","151.20","56.70","86.33","2","302.40","113.40","172.66","Openings: 109, 161","September 17, 2010 3:01 pm","Page -1 of 1"
"SC","Lockset ND50PD SPA 626","428.00","160.50","244.36","7","2996.00","1123.50","1710.52","Openings: 105, 112, 114, 115, 116, 160, 164","September 17, 2010 3:01 pm","Page -1 of 1"

So how does your code remove the:

"Product Summary","200009 : SCA La Veta ASC","

which can't be there. And put in a break after the headers so that the data falls under the headers instead of as headers? I don't see any of that and that's what I was up against when trying to do this.