Dratted Dat file !!

[Bagsy Baker]

I am trying to open a DAT file in excel, add an extra field, then SaveAs a DAT file. The open, amend, and SaveAs work, ... sort of. If I set the SaveAs type to CSV, it puts extra quotes around the text fields. If I remove the quotes on import, it leaves them off when it saves. The quote marks are required to tell the program that opens this DAT file which fields are text. I have even tried inserting a column between the columns with information and putting in comma marks and SaveAs .txt, but I hit the same road block. Does anyone have an avenue of possible hope? Here is a sample of data that I am trying to manipulate.

#V1.0
*,461,1111111,,,123
1,2,"IXX",324,800,,,"LAST",24
2,2,"IXX",400,450,"XXX",,,14
*462,1111111,,,111
1,5,"IXC",400,500,,,,,11
#
This is the format it starts in, and must end in the same format. No ""IXC"" or IXC only.
The overall goal is to add the number after the * to the end of the line that starts with a number. The information starts and ends with the # sign. All I can't figure out is how to SaveAs shown above.

 

[kkknie]

I've read a few posts concerning this and have had problems myself. From what I have tried, I can find no way to SaveAs without adding quotes. Here is a link to my last post on that topic:

http://www.mrexcel.com/board/viewtopic.php?topic=7233&forum=2

If you are working with files and doing the exact same thing to them, you may be able to input them and output them without ever "importing" them into excel (using the Open Filename for Input and Open Filename for Output commands in VBS.

I'm headed to the golf course in 20 minutes, but I'd be glad provide more help/info if you could clarify just a bit more...

K

 

[Legacy 98055]

Bagsy
Reviving this post in response to your private message. Maybe some other ideas will popup.
For now,
This would not be a huge problem.
Worst case scenario would be to export the file using a binary write(Character by character).
Please post an exact replica of the data import.
Then explain what you need to do with this data in Excel.
Then show the exact replica of what the data should look like after it has been changed(the export)
Thanks,
Tom

 

[Bagsy Baker]

Thank you for agreeing to look at this problem. I've been working at it the last month or so and keep ending up at the same road blocks.

Here is the DAT file as exported by our order entry system.
<V1.0>, "WinIG export file"
*, "4648136", "111111", "", "042302", "", ""
001, 00002, 0, "ICL301 ", 00376, 00714,, "1/2 IN AS ", ""
002, 00002, 0, "ICL301 ", 00554, 00816,, "1/2 IN AS ", ""
003, 00001, 0, "ICL301 ", 00511, 01121,, "1/2 IN AS ", ""
004, 00001, 0, "ICL301 ", 00683, 00573,, "1/2 IN AS ", ""
*, "4648137", "111111", "", "042302", "", ""
001, 00004, 0, "ICL301 ", 00590, 01065,, "1/2 IN AS ", ""
002, 00001, 0, "ICL301 ", 00403, 01116,, "1/2 IN AS ", ""
003, 00009, 0, "ISG398 ", 00490, 01395,, "1/2 IN AS ", "ARGON "
004, 00006, 0, "ISG398 ", 00388, 01395,, "1/2 IN AS ", "ARGON "
#

The # tells the next program (a glass cutting optimizer) where the file starts and ends. The * tells the next program that this is an order header line. The lines that start with a number are order lines for the preceeding order number. The fields that are within quote marks are text fields and must remain as text fields. The number fields must remain as number fields. The leading zeros are not required. What I have to do is add a sixteenth field which will be the value of the order number (field after the * mark) and the line number ( the first field of the order line). After doing this operation, save the file as a dat or txt file and close. The above example should look like this;

<V1.0>, "WinIG export file"
*, "4648136", "111111", "", "042302", "", ""
001, 00002, 0, "ICL301 ", 00376, 00714,, "1/2 IN AS ", "",,,,,,,"4648136-1"
002, 00002, 0, "ICL301 ", 00554, 00816,, "1/2 IN AS ", "",,,,,,,"4648136-2"
003, 00001, 0, "ICL301 ", 00511, 01121,, "1/2 IN AS ", "",,,,,,,"4648136-3"
004, 00001, 0, "ICL301 ", 00683, 00573,, "1/2 IN AS ", "",,,,,,,"4648136-4"
*, "4648137", "111111", "", "042302", "", ""
001, 00004, 0, "ICL301 ", 00590, 01065,, "1/2 IN AS ", "",,,,,,,"4648137-1"
002, 00001, 0, "ICL301 ", 00403, 01116,, "1/2 IN AS ", "",,,,,,,"4648137-2"
003, 00009, 0, "ISG398 ", 00490, 01395,, "1/2 IN AS ", "ARGON ",,,,,, "4648137-3"
004, 00006, 0, "ISG398 ", 00388, 01395,, "1/2 IN AS ", "ARGON ",,,,,, "4648137-4"
#
What this allows me to do is print the order number-line number on the spacer (We manufacture glass sealed units) on the spacer so that if we are required to remake a unit (warranty), the service tech needs only to read the printed number, and all the other information about size and configuration can be read from a table I save in Excel using a find. The only spot I'm truly hung up on is the SaveAs. I end up with no quotes or double quotes, neither of which will allow me to import properly. At peak periods, I may be running 700 to 1000 of these lines per day which makes manual correction a nightmare. By rights, the original creater of the optimizer software should have set something like this up, but they didn't, and won't, and have locked their code up so tight that it is impossible correct it within the program itself. Any help you could give would be greatly appreciated.

 

[Legacy 98055]

This works except for one potential problem.
The Print statement place a linefeed onto the end of each line(chr(10)).
If your app reads the file as text, then no problem...

See if it works.
If not, we'll need to find a workaround...
I'll get around to commenting the code this weekend.

Tom

<pre>

Private Sub cmdEditFiles_Click()
Dim FileToOpen, FileToSave, Pos
Dim CurVal, CurLine, AcsVal
Dim GetOrderNum As Boolean, NoOrder As Boolean
Dim OrderNumString As String
Dim ColumnsCntr As Integer
Dim SubOrderNumber As String
Dim Changes As Boolean, DeleteFiles As Boolean
Dim PlaceRow As Long
Dim DisplayChanges As Boolean
On Error Resume Next
Range("A12:C65536").ClearContents
Range("A11").Select
If Range("AA1") <> "" Then ChDir Range("AA1")
If Err.Number = 76 Then
Err.Clear
Range("AA1") = ""
End If
FileToOpen = Application.GetOpenFilename("Data Files (*.dat), *.dat", _
, "Choose the data file to edit.")
If FileToOpen = False Then Exit Sub
Pos = InStrRev(FileToOpen, "")
Range("AA1") = Left(FileToOpen, Len(FileToOpen) - (Len(FileToOpen) - Pos + 1))
Application.Wait Now + #12:00:01 AM#
FileToSave = Application.GetSaveAsFilename(Range("AA2"), _
"Data Files (*.dat), *.dat", , "Choose the name and path to save to")
If FileToSave = False Then Exit Sub
Range("AA2") = FileToSave
If FileToSave = FileToOpen Then
MsgBox "The file being opened cannot have the " & _
"the same name as the file being saved."
Exit Sub
End If
NoOrder = True
PlaceRow = 12
DeleteFiles = chkDelete.Value
DisplayChanges = chkDisplay.Value
Close #1
Close #2
Open FileToOpen For Input As #1
Open FileToSave For Output As #2
Do While Not EOF(1)
ColumnsCntr = 1
Do Until AcsVal = 13
If EOF(1) Then Exit Do
CurVal = Input(1, #1)
AcsVal = Asc(CurVal)
If AcsVal = 42 Then
GetOrderNum = True
NoOrder = False
End If
If AcsVal = 44 Then ColumnsCntr = ColumnsCntr + 1
If AcsVal = 35 Then
NoOrder = True
DisplayChanges = False
End If
If AcsVal <> 10 And AcsVal <> 13 Then _
CurLine = CurLine & CurVal
Debug.Print CurVal; AcsVal
Loop
AcsVal = 0
If DisplayChanges Then
ActiveSheet.Cells(PlaceRow, 1) = "(Original)"
ActiveSheet.Cells(PlaceRow, 2) = CurLine
PlaceRow = PlaceRow + 1
End If
If GetOrderNum Then
OrderNumString = Mid(CurLine, 5, 7) & "-"
GetOrderNum = False
Else
If Not NoOrder Then
If Val(Left(CurLine, 1)) <> 0 Then
SubOrderNumber = Left(CurLine, 3)
ElseIf Val(Left(CurLine, 2)) <> 0 Then
SubOrderNumber = Mid(CurLine, 2, 2)
ElseIf Val(Left(CurLine, 3)) <> 0 Then
SubOrderNumber = Mid(CurLine, 3, 1)
End If
Do Until ColumnsCntr = 16
ColumnsCntr = ColumnsCntr + 1
CurLine = CurLine & Chr(44)
Loop
Changes = True
CurLine = CurLine & Chr(34) & OrderNumString & SubOrderNumber & Chr(34)
If DisplayChanges Then
ActiveSheet.Cells(PlaceRow, 2) = "(Edited)"
ActiveSheet.Cells(PlaceRow, 3) = CurLine
PlaceRow = PlaceRow + 2
End If
End If
End If
Print #2, CurLine
If Not Changes And DisplayChanges Then
ActiveSheet.Cells(PlaceRow, 2) = "(Un-Changed)"
PlaceRow = PlaceRow + 2
End If
Changes = False
Debug.Print CurLine
CurLine = Empty
Loop
Close #1
Close #2
If DeleteFiles Then Kill FileToOpen
End Sub

</pre>