Error Checking in Excel
Thanks Thanks:  0
Likes Likes:  0
Results 1 to 5 of 5

Thread: Dratted Dat file !!

  1. #1
    New Member
    Join Date
    Feb 2002
    Location
    Sackville
    Posts
    39
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

    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.

  2. #2
    Board Regular
    Join Date
    Apr 2002
    Location
    Greenwood, SC
    Posts
    677
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

    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

  3. #3
    Board Regular
    Join Date
    Mar 2002
    Location
    Cincinnati, Ohio, USA
    Posts
    6,824
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

    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

  4. #4
    New Member
    Join Date
    Feb 2002
    Location
    Sackville
    Posts
    39
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

    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.

  5. #5
    Board Regular
    Join Date
    Mar 2002
    Location
    Cincinnati, Ohio, USA
    Posts
    6,824
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

    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



Some videos you may like

User Tag List

Like this thread? Share it with others

Like this thread? Share it with others

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •