Parse Tab Delimited Text File

[ThomasLafferty]

I have written a routine in VBA for Excel XP which imports a tab delimited text file into my workbook. The original file looks like this:

"B3"	"2421835"	"2421835"	"TP"	05/17/07	1175.00	"CEI"	05/06/07
"N9"	"IT"	"KUP"
"N9"	"BF"	"YA"
"N9"	"PO"	"CA23WHSEF"
"N1"	"SH"	"CONOCO PHILLIPS"
"N3"	"KUPARUK MATERIALS WHSE"	""
"N4"	"PRUDHOE BAY"	"AK"	"99734"	"US"
"N1"	"CN"	"UNITECH OF ALASKA"
"N3"	"7600 KING STREET"	""
"N4"	"ANCHORAGE"	"AK"	"99518"	"US"
"N7"	"KW"	"5690"
"LX"	1
"LXN9"	"PH"	"S"
"L5"	1	"CONNEXS 20' M/T FOR REFIL"	"BKHL"	"N"
"L0"	1	10000	"LC"	10000	"N"	2	"PCS"	"L"
"L1"	1	1000.00	"FR"	1000.00	"400"	"FREIGHT CHARGE"
"L1"	2	175.00	"FR"	175.00	"405"	"17.5% FUEL SURCHARGE"
"L3"	10000	"G"	1175.00

When I import it to Excel, all the quotes are stripped out, so it looks like this:

B3	2421835	2421835	TP	05/17/07	1175.00	CEI	05/06/07
N9	IT	KUP
N9	BF	YA
N9	PO	CA23WHSEF
N1	SH	CONOCO PHILLIPS
N3	KUPARUK MATERIALS WHSE	
N4	PRUDHOE BAY	AK	99734	US
N1	CN	UNITECH OF ALASKA
N3	7600 KING STREET	
N4	ANCHORAGE	AK	99518	US
N7	KW	5690
LX	1
LXN9	PH	S
L5	1	CONNEXS 20' M/T FOR REFIL	BKHL	N
L0	1	10000	LC	10000	N	2	PCS	L
L1	1	1000.00	FR	1000.00	400	FREIGHT CHARGE
L1	2	175.00	FR	175.00	405	17.5% FUEL SURCHARGE
L3	10000	G	1175.00

The problem is that when I export it back out as text, which is a necessity, the quotes are not put back in, and they MUST be there. If I import the same file with the text qualifier set to xlSingleQuote, it leaves the quotes where they need to be, but when I save the file as a text file, the quotes are doubled, like this:

"""B3"""	"""2421835"""	"""2421835"""	"""TP"""	5/17/2007	1175	"""CEI"""	5/6/2007	
"""N9"""	"""IT"""	"""KUP"""						
"""N9"""	"""BF"""	"""YA"""						
"""N9"""	"""PO"""	"""CA23WHSEF"""						
"""N1"""	"""SH"""	"""CONOCO PHILLIPS"""						
"""N3"""	"""KUPARUK MATERIALS WHSE"""	""""""						
"""N4"""	"""PRUDHOE BAY"""	"""AK"""	"""99734"""	"""US"""				
"""N1"""	"""CN"""	"""UNITECH OF ALASKA"""						
"""N3"""	"""7600 KING STREET"""	""""""						
"""N4"""	"""ANCHORAGE"""	"""AK"""	"""99518"""	"""US"""				
"""N7"""	"""KW"""	"""5690"""						
"""LX"""	1							
"""LXN9"""	"""PH"""	"""S"""						
"""L5"""	1	"""CONNEXS 20' M/T FOR REFIL"""	"""BKHL"""	"""N"""				
"""L0"""	1	10000	"""LC"""	10000	"""N"""	2	"""PCS"""	"""L"""
"""L1"""	1	1000	"""FR"""	1000	"""400"""	"""FREIGHT CHARGE"""		
"""L1"""	2	175	"""FR"""	175	"""405"""	"""17.5% FUEL SURCHARGE"""	
"""L3"""	10000	"""G"""	1175

I am making modifications to the imported file in my workbook and need to be able to save it so that only single quotes appear.

I have tried using code to add the quotes directly in the sheet before saving as text, and this is successful, but the text file ends up with double quotes. Any ideas?


Born once die twice; born twice die once.

 

[Gavona]

Use a text editor such as notepad to replace double quotes with single quotes?
I am sure i have seen code solutions to this sort of issue - have you searched the vba forum? eg thread707-1265211

Gavin

 

[ThomasLafferty]

Thanks for the reply!
I have tried the above to no avail, so I have decided to write directly to the file. I have investigated the forum and found several attempts at this, but can't quite get it to work. I need a carriage return at the end of each line, and I also need to strip out the last tab Chr(9) since it will be extra. Here's what I have so far, and it works, but I need to get the extra tab out and get in the line feed.

'determine last invoice row on send sheet
    LastSendRow = Worksheets("Send").Range("A65536").End(xlUp).Row
    Open "\\cts-mail\inetdata\Data\EDI\ConocoPhillips 210\Check\Test.txt" For Output As #1
    
    Dim LastSendColumn As Integer
    For WriteEDIRow = 1 To LastSendRow
        LastSendColumn = Worksheets("Send").Range("IV" & WriteEDIRow).End(xlToLeft).Column
        For WriteEDIColumn = 1 To LastSendColumn
                If WriteEDIColumn < LastSendColumn Then
                    strEDI = strEDI & Worksheets("Send").Cells(WriteEDIRow, WriteEDIColumn).Value _
& Chr(9) 'add a tab
                End If
        Next WriteEDIColumn
    Next WriteEDIRow
    Print #1, strEDI
    Close #1

Born once die twice; born twice die once.

 

[SkipVought]

"I have tried the above to no avail"

Replace
[tt]
"""
[/tt]
with
[tt]
"
[/tt]


Skip,
[sub]
When a diminutive clarvoyant had disappeared from detention, headlines read...
Small Medium at Large[/sub]

 

[ThomasLafferty]

Hi Skip - I should have been a bit more specific in my reply. The problem remains that if I leave the quotes in the sheet tab in my book which I then save as text, then when I open the text file, I discover that "B3" has become """B3""" in the text file itself, not in the sheet tab - it remains as "B3" in the sheet. If I save the sheet as a text file and the cell value displays as B3, when I look at the text file, it will display B3, but I need it to be "B3".

If the sheet contains "B3", and I write it to the text file directly using code as shown above, the quoted value remains as it: "B3". The problem I am now having is that I can't seem to get a carriage return at the end of each line, nor have I figured out how to strip out the last tab character from each line. Incidentally, the resulting text file MUST have all quoted values quoted as shown in my first example - it will be submitted via EDI after analysis by Excel. For various reasons, I would rather have help from the Excel experts on this one, rather than posting in the EDI forum since I have my solution nearly finished, and it is Excel based.

To see the behavior I am talking about with the misbehaving quotes, copy the following into a blank work sheet at cell A1, then save the active sheet as a tab delimited text file.

"B3"    "2421835"    "2421835"    "TP"    05/17/07    1175.00    "CEI"    05/06/07
"N9"    "IT"    "KUP"
"N9"    "BF"    "YA"
"N9"    "PO"    "CA23WHSEF"
"N1"    "SH"    "CONOCO PHILLIPS"
"N3"    "KUPARUK MATERIALS WHSE"    ""
"N4"    "PRUDHOE BAY"    "AK"    "99734"    "US"
"N1"    "CN"    "UNITECH OF ALASKA"
"N3"    "7600 KING STREET"    ""
"N4"    "ANCHORAGE"    "AK"    "99518"    "US"
"N7"    "KW"    "5690"
"LX"    1
"LXN9"    "PH"    "S"
"L5"    1    "CONNEXS 20' M/T FOR REFIL"    "BKHL"    "N"
"L0"    1    10000    "LC"    10000    "N"    2    "PCS"    "L"
"L1"    1    1000.00    "FR"    1000.00    "400"    "FREIGHT CHARGE"
"L1"    2    175.00    "FR"    175.00    "405"    "17.5% FUEL SURCHARGE"
"L3"    10000    "G"    1175.00

If I could automate Word with Excel to do the replace of """ with ", I would gladly do it. The problem is that I would need a reference to both the Office 10 and Office 12 libraries since we will be migrating to Office 2007 within the next 3 months. I tried using Word (ie Winword.application) , but I ran into problems with the file getting corrupted, or else not being released by Excel prior to being modified by Word. I can give more specifics if needed.

Incidentally, 'tis good to hear from you again - your replies are almost always dead on!

Born once die twice; born twice die once.

 

[SkipVought]

Use a text editor such as notepad to replace double quotes with single quotes?

I have tried the above to no avail,

IT WORKS!!!!!!!!!!!!!!

I showed you how, but SOMEHOW you insist on changing in your WORKBOOK!!!!!


Skip,
[sub]
When a diminutive clarvoyant had disappeared from detention, headlines read...
Small Medium at Large[/sub]

 

[ThomasLafferty]

Hi Skip - you are right! If I open Word and use it to do an edit replace of """ with ", that will certainly do the trick. The problem is that I need this to be a hands off process. I have a routine which correctly analyzes which invoices pass muster and separates them from those that don't. Currently, I am only doing this for a total of 5 customers and perhaps 50 invoices a day. If I can get this automated, that is analysis and sending, the company I am working for plans to do this for an additional 800 customers.

The way the process worked before I started analyzing with Excel, the text file had to be opened and manually read to find error. By importing into my book, I have been able to separate these out and put them into text files. If I get a coded solution which only deals with the text file and never brings it into my book, it will likely be totally over my head and I will become dependant on someone else's coding ability. If I manually open the text file, it will take up my time to do this, and I will also have to then save the file in the folder which sends the text file to our EDI recipient.

If I can use Excel for the process, I stand a chance of automating the entire thing and will be able to bring more customers on board.

I agree with you that the """ to " works in a text editor, but that's what I am trying to avoid. By trying to no avail, I meant what I had tried, not what you or Gavin suggest.

Other ideas? (Other than having someone map out each EDI customer for us at $900 per map when I nearly have the solution?)

Thanks in advance guys - I appreciate it!
Tom

Born once die twice; born twice die once.

 

[SkipVought]

Use the VB Print# statement et al.

Skip,
[sub]
When a diminutive clarvoyant had disappeared from detention, headlines read...
Small Medium at Large[/sub]

 

[Welshbird]

At the risk of bunging a spanner in the works... Is excel definitely the right tool for the job here? It sounds to me like Access might be better - and you can certainly export in any format you chose.

(Only a suggestion though).



Fee

The question should be [red]Is it worth trying to do?[/red] not [blue] Can it be done?[/blue]

 

[ThomasLafferty]

Hi willif -
Access is an interesting notion, but the problems I see is that there is no clear indication of what the field names would be - each "record" does not have a header. The start of the invoice is flagged with "B3" and there is a series of values, and the end of the invoice is flagged with "L3" and a series of values. In between, the lines are of varying lengths, and there is not much rhyme or reason to it other than a set of rules which have to apply to the invoice as a whole. I nearly have the whole thing solved via Excel, and it wasn't that difficult - I'm just having a bit of a bugaboo with the output.

Skip -
I'm already using the Print# statement, but it doesn't seem to be adding the carriage return that I need at the end of each row. Here's the current state of my code:

[COLOR=green]'determine last invoice row on send sheet[/color]
    LastSendRow = Worksheets("Send").Range("A65536").End(xlUp).Row
    [COLOR=blue]Open [/color]"\\cts-mail\inetdata\Data\EDI\ConocoPhillips 210\Check\Test.txt" [COLOR=blue]For Output As [/color]#1
    
    [COLOR=blue]Dim [/color]LastSendColumn [COLOR=blue]As Integer[/color]
    [COLOR=blue]For [/color]WriteEDIRow = 1 [COLOR=blue]To[/color] LastSendRow
        LastSendColumn = Worksheets("Send").Range("IV" & WriteEDIRow).End(xlToLeft).Column
        [COLOR=blue]For [/color]WriteEDIColumn = 1 [COLOR=blue]To [/color]LastSendColumn
                [COLOR=blue]If [/color]WriteEDIColumn < LastSendColumn [COLOR=blue]Then[/color]
                    strEDI = strEDI & Worksheets("Send").Cells(WriteEDIRow, WriteEDIColumn).Value & Chr(9) [COLOR=green]'add a tab[/color]
                [COLOR=blue]End If[/color]
        [COLOR=blue]Next[/color] WriteEDIColumn
            strEDI = strEDI & Chr$(13) [COLOR=green]'add a carriage return[/color]
    [COLOR=blue]Next [/color]WriteEDIRow
    [COLOR=blue]Print[/color] #1, strEDI
    [COLOR=blue]Close[/color] #1

Born once die twice; born twice die once.

 

[ThomasLafferty]

Got it! It turns out I needed to add a line feed after the carriage return, and only add the tab if I hadn't arrived at the last column. Here's the ammended code:

[COLOR=green]'determine last invoice row on send sheet[/color]
    LastSendRow = Worksheets("Send").Range("A65536").End(xlUp).Row
    [COLOR=blue]Open [/color]"\\cts-mail\inetdata\Data\EDI\ConocoPhillips 210\Check\Test.txt" [COLOR=blue]For Output As [/color]#1
    
    [COLOR=blue]Dim [/color]LastSendColumn [COLOR=blue]As Integer[/color]
    [COLOR=blue]For [/color]WriteEDIRow = 1 [COLOR=blue]To[/color] LastSendRow
        LastSendColumn = Worksheets("Send").Range("IV" & WriteEDIRow).End(xlToLeft).Column
        [COLOR=blue]For [/color]WriteEDIColumn = 1 [COLOR=blue]To[/color] LastSendColumn
                strEDI = strEDI & Worksheets("Send").Cells(WriteEDIRow, WriteEDIColumn).Value
                [COLOR=blue]If [/color]WriteEDIColumn < LastSendColumn Then
                    strEDI = strEDI & Chr(9) [COLOR=green]'add a tab[/color]
                [COLOR=blue]End If[/color]
        [COLOR=blue]Next[/color] WriteEDIColumn
        [COLOR=blue]If[/color] WriteEDIRow < LastSendRow [COLOR=blue]Then[/color]
            strEDI = strEDI & Chr(13) & Chr(10)  [COLOR=green]'add a carriage return[/color]
        End If
    [COLOR=blue]Next[/color] WriteEDIRow
    [COLOR=blue]Print[/color] #1, strEDI
    [COLOR=blue]Close[/color] #1

Born once die twice; born twice die once.

 

[SkipVought]

Check out vbLf, vbCr & vbCrLf constants.

Skip,
[sub]
When a diminutive clarvoyant had disappeared from detention, headlines read...
Small Medium at Large[/sub]

 

[Welshbird]

Thomas - I have done a similar thing in access and had a load_table with set headings and then a macro that copied that whole table to a new table leaving the load table empty for next time.

Not got a particular axe to grind though - what ever works for you is just the ticket.

Fee

The question should be [red]Is it worth trying to do?[/red] not [blue] Can it be done?[/blue]

 

[ThomasLafferty]

Skip and willif - I'm grateful for the suggestions and will take them under advisement.

Thanks again guys!

Tom

Born once die twice; born twice die once.