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/viewtop...c=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

[Tom Schreiner]

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.
, "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;

, "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.

[Tom Schreiner]

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

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