Page 1 of 2 12 LastLast
Results 1 to 15 of 18
  1. #1
    WS Lounge VIP sdckapr's Avatar
    Join Date
    Jul 2002
    Location
    Pittsburgh, Pennsylvania, USA
    Posts
    11,225
    Thanks
    14
    Thanked 342 Times in 335 Posts

    Re: Open File for Input (XP02)

    I do not see the problem in XL97 or do not understand the setup.

    If I create a text file with the first line literally (as you have listed):
    "John","My Name","12ab3","3256","BUnit, Inc"

    Save it and run your code (filling the string variable "Var1" with the path and name of the text file)
    I put that entire line listed above all in cell A1.
    Even the msgbox is one line.

    Am I misunderstanding something or is there really this large a difference between XP and xl97?

    Steve

  2. #2
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Open File for Input (XP02)

    Steve,

    In Excel 2002, I get exactly the same results as you get in Excel 97.

  3. #3
    Silver Lounger
    Join Date
    Dec 2000
    Location
    California, USA
    Posts
    1,758
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Open File for Input (XP02)

    Steve,

    The CSV file is generated out of PeopleSoft's general ledger module (Excel software included in the app). If I open the CSV file from XL and resave, there is no issue.

    I'm not quite sure why but do you think it relates to the 3rd party providor?

    John

  4. #4
    Silver Lounger
    Join Date
    Dec 2000
    Location
    California, USA
    Posts
    1,758
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Open File for Input (XP02)

    I am trying to open a CSV file for Input and encounter a memory issue. The CSV file contains fields: "User","Description","Prim Perm List","Unit","Descr" with data
    beginning and ending in quotes ie "John","My Name","12ab3","3256","BUnit, Inc".

    Sample code:
    Dim LineofText As String
    Open Var1 For Input As #1
    Do While Not EOF(1)
    Line Input #1, LineofText
    Range("A1") = LineofText
    Loop
    Close #1
    End If

    Only one line of text should be entered in cell A1 at a time. If I create a "Msgbox LineofText" I see more than one line of text instead of just one line.

    Any ideas why more than one line?
    Thanks,
    John

  5. #5
    Uranium Lounger
    Join Date
    Jan 2001
    Location
    South Carolina, USA
    Posts
    7,295
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Open File for Input (XP02)

    It sounds like the program that is writing the CSV file is ending each line with a line feed (CHR(10)) without a Carriage Return. In that case, Line Input will treat the entire file as a singel line.
    Legare Coleman

  6. #6
    WS Lounge VIP sdckapr's Avatar
    Join Date
    Jul 2002
    Location
    Pittsburgh, Pennsylvania, USA
    Posts
    11,225
    Thanks
    14
    Thanked 342 Times in 335 Posts

    Re: Open File for Input (XP02)

    A question to make sure we understsnd the problem:
    What do you want or expect to be in cell A1 after your code is run?

    I expected (and got) 1 line. That line was:
    "John","My Name","12ab3","3256","BUnit, Inc"

    From what I understand, You said the problem was that you got more than 1 line. Just making a hypothesis: are you defining this 1 line as actually 5 lines?
    John
    My Name
    12ab3
    3256
    BUnit, Inc

    And the problem is you expect the code to give you just "part of a line" (John) when it reads 1 line, but it is giving you the whole line ("John","My Name","12ab3","3256","BUnit, Inc")? This would mean that you are not getting multiple lines, but that you want to parse it.

    Is this what you are after? If so could you give more details about what you want done and we could help with the coding.

    Steve

  7. #7
    Silver Lounger
    Join Date
    Dec 2000
    Location
    California, USA
    Posts
    1,758
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Open File for Input (XP02)

    Steve,

    The attached file depicts what issue I'm having. One would think that only one line would be written to cell A1 not everything. I truly believe it is how PeopleSoft's XL is creating the CSV file. Legare may have hit it right on the head.

    John

  8. #8
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Open File for Input (XP02)

    John,

    Could you attach a sample CSV file?

  9. #9
    Silver Lounger
    Join Date
    Dec 2000
    Location
    California, USA
    Posts
    1,758
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Open File for Input (XP02)

    Hans,

    I tried saving sample text from the CSV file in question: when I save it either by XL or a text editor to a CSV file there is no issue. Unfortunately I can not send the file itself due to the contents of the file. I'm going to circle back with my IT group to verify how XL is handling the initial save as a CSV file.

    Thanks,
    John

  10. #10
    WS Lounge VIP sdckapr's Avatar
    Join Date
    Jul 2002
    Location
    Pittsburgh, Pennsylvania, USA
    Posts
    11,225
    Thanks
    14
    Thanked 342 Times in 335 Posts

    Re: Open File for Input (XP02)

    If Legare is correct (and it seems like a valid hypothesis at this time), you could still parse the data from Range A1 by looking for the Linefeed character either during the input stage or afterwards using formulas. If this is a routine operation, you could use a macro to parse it.

    I agree with Hans, we need to see the csv file you want to work with and what you want the output to be. Do you actually want all those "subparts" in one cell or do you want them in separate columns?

    Steve

  11. #11
    WS Lounge VIP sdckapr's Avatar
    Join Date
    Jul 2002
    Location
    Pittsburgh, Pennsylvania, USA
    Posts
    11,225
    Thanks
    14
    Thanked 342 Times in 335 Posts

    Re: Open File for Input (XP02)

    Try this code. It assumes that a line delimiter is vbLF [chr(10)] and a col delimiter is comma. It eliminates the quotes. (It actually replaces commas between "quoted" sections with a chr(1) to use this as a delimiter, since you have some commas also that are not delimiters for columns.

    Change as needed.

    Steve

    <pre>Option Explicit
    Sub ImportSomeDataDelimited()
    Dim wks As Worksheet
    Dim lRow As Long
    Dim iCol As Integer
    Dim sLineDelimiter As String
    Dim sPathFilename As String
    Dim sLine As String
    Dim sColDelimiter As String
    Dim sColDelNew As String
    Dim iCurrDelimiter As Integer
    Dim iNextLineDelimiter As Integer
    Dim iNextColDelimiter As Integer
    Dim af As WorksheetFunction

    sPathFilename = "C:temp.txt"
    sLineDelimiter = vbLf
    sColDelimiter = ","
    sColDelNew = Chr(1)
    Set af = Application.WorksheetFunction
    Set wks = Worksheets.Add
    lRow = 1
    Open sPathFilename For Input As #1
    Do While Not EOF(1)
    Line Input #1, sLine
    sLine = af.Substitute(sLine, Chr(34) & sColDelimiter & Chr(34), sColDelNew)
    sLine = af.Substitute(sLine, Chr(34), "")
    sLine = sLine & sColDelNew & sLineDelimiter
    sLine = af.Substitute(sLine, Chr(34), "")

    iNextLineDelimiter = InStr(sLine, sLineDelimiter)
    Do While iNextLineDelimiter <> 0
    iNextColDelimiter = InStr(sLine, sColDelNew)
    iCol = 1
    Do While iNextColDelimiter <> 0
    wks.Cells(lRow, iCol) = _
    Left(sLine, iNextColDelimiter - 1)
    sLine = Mid(sLine, iNextColDelimiter + 1)
    iNextColDelimiter = InStr(sLine, sColDelNew)
    iCol = iCol + 1
    Loop
    sLine = Mid(sLine, iNextLineDelimiter + 1)
    iNextLineDelimiter = InStr(sLine, sLineDelimiter)
    lRow = lRow + 1
    Loop
    Loop
    Close #1
    Set af = Nothing
    Set wks = Nothing
    End Sub</pre>


  12. #12
    Silver Lounger
    Join Date
    Dec 2000
    Location
    California, USA
    Posts
    1,758
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Open File for Input (XP02)

    Steve,

    I run into an issue where Cell M1 should be "YEAR" but starts combining what should be Cell A2. I've tweaked the code a bit to account for the 13 columns and then proceed to the next row. Unfortunately Cell A2 reflects the incorrect information.

    I have attached a SAMPLE file which should demonstrate my issue.

    Thanks,
    John

  13. #13
    WS Lounge VIP sdckapr's Avatar
    Join Date
    Jul 2002
    Location
    Pittsburgh, Pennsylvania, USA
    Posts
    11,225
    Thanks
    14
    Thanked 342 Times in 335 Posts

    Re: Open File for Input (XP02)

    I made a few changes. See if this works (it does on the sample)

    Steve
    <pre>Option Explicit
    Sub ImportSomeDataDelimited()
    Dim wks As Worksheet
    Dim lRow As Long
    Dim iCol As Integer
    Dim sLineDelimiter As String
    Dim sPathFilename As String
    Dim sLine As String
    Dim sNextLine As String
    Dim sColDelimiter As String
    Dim sColDelNew As String
    Dim iCurrDelimiter As Integer
    Dim iNextLineDelimiter As Integer
    Dim iNextColDelimiter As Integer
    Dim af As WorksheetFunction

    sPathFilename = "C:temp.txt"
    sLineDelimiter = vbLf
    sColDelimiter = ","
    sColDelNew = Chr(1)
    Set af = Application.WorksheetFunction
    Set wks = Worksheets.Add
    lRow = 1
    Open sPathFilename For Input As #1
    Do While Not EOF(1)
    Line Input #1, sLine
    sLine = af.Substitute(sLine, Chr(34) & sColDelimiter & Chr(34), sColDelNew)
    sLine = af.Substitute(sLine, Chr(34), "")
    sLine = sLine & sColDelNew & sLineDelimiter
    sLine = af.Substitute(sLine, Chr(34), "")

    iNextLineDelimiter = InStr(sLine, sLineDelimiter)
    Do While iNextLineDelimiter <> 0
    iNextColDelimiter = InStr(sLine, sColDelNew)
    sNextLine = Mid(sLine, iNextLineDelimiter + 1)
    sLine = Left(sLine, iNextLineDelimiter - 1)
    iCol = 1
    Do While iNextColDelimiter <> 0
    wks.Cells(lRow, iCol) = _
    Left(sLine, iNextColDelimiter - 1)
    sLine = Mid(sLine, iNextColDelimiter + 1)
    iNextColDelimiter = InStr(sLine, sColDelNew)
    iCol = iCol + 1
    Loop
    wks.Cells(lRow, iCol) = sLine
    sLine = sNextLine
    iNextLineDelimiter = InStr(sLine, sLineDelimiter)
    lRow = lRow + 1
    Loop
    Loop
    Close #1
    Set af = Nothing
    Set wks = Nothing
    End Sub</pre>


  14. #14
    Silver Lounger
    Join Date
    Dec 2000
    Location
    California, USA
    Posts
    1,758
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Open File for Input (XP02)

    Steve,

    It worked. I have to review what you tweaked.

    Many thanks,
    John

  15. #15
    Silver Lounger
    Join Date
    Dec 2000
    Location
    California, USA
    Posts
    1,758
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Open File for Input (XP02)

    Steve,

    I receive the following error message when I change the file from the SAMPLE to the one I would like to use. The file formats of the two are the same.


    John

Page 1 of 2 12 LastLast

Posting Permissions

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