Input #1, <string problem>

M

MARKG

Board Regular
Joined
Jul 3, 2002
Messages
56
Problem: String is broken into 2 parts within a delimeter.
Goal: To keep each String/Segment in tact regarless of Alpha/Numeric.

I have a .csv file with 26 segments (commas). This file contains customer data: PO#, Order#, Carton ID, Address 1, Address 2, Address 3, etc.

I've written code to capture all 26 segments as: Input #1, Seg1, Seg2, ... ,Seg26. The variables work fine until it encounter a string with Numeric first then Alpha within the same segment.

For this example we have a string such as (...,187 Main Street, PO Box 369,...)
Seg4 = Address <187 Main Street>
Seg5 = PO Box <PO Box 369>

The Segment Variables in Input #1, get shifted when it encounters the numeric portion of the address...so the result becomes:
Segment4 = 187
Segment5 = Main Street
Segment 6 = PO Box 369.

So on my next loop when ReadLine is executed...all the variables are shifted and I can't focus on the specific segment that I need to do something to it.

It delimits when it encounters Numeric then Alpha within the string, but accepts the entire string into the variable when its Alpha then Numeric within a delimeter.

Any suggestions how I can keep the entire string in tact within each delimeter?
Thanks!
Mark
 

Excel Facts

Will the fill handle fill 1, 2, 3?
Yes! Type 1 in a cell. Hold down Ctrl while you drag the fill handle.
Sort by date Sort by votes
MARKG said:
I have a .csv file with 26 segments (commas). This file contains customer data: PO#, Order#, Carton ID, Address 1, Address 2, Address 3, etc.
Click to expand...
Can you copy a few lines from the CSV file (open it in a text processor such as NotePad) and paste them into a response here (you can change names, numbers if you want to hide information, I'm only interested in seeing the file's structure as it is stored on the hard drive)?
 
Upvote 0
Rick Rothstein said:
Can you copy a few lines from the CSV file (open it in a text processor such as NotePad) and paste them into a response here (you can change names, numbers if you want to hide information, I'm only interested in seeing the file's structure as it is stored on the hard drive)?
Click to expand...

Here's a few lines, Rick; (the end of one Line/record is the comma after "Client Number". That comma represents the 26th segment.

The specific segment we should discuss moving forward is Segment 6 (Address 1 field). Currently it splits 2222 and then places BETRAL DRIVE into Segment 7 variable instead of 2222 BETRAL DRIVE into Segment 6 variable.

Consignee PO#,CCC Order#,CCC WH,Carton ID,Ship To Name,Address 1,Address 2,Address 3,City,State,Zip Code,Country,Weight,Container ID,Length,Width,Height,Trailer #,H File#,C&C BOL#,Carrier,Collect/Prepaid,Billing Acct#,Ship To#,Client Number,
61299A,137559,02,="00000236500192598098",CUSTOMER ABC #28,2222 BETRAL DRIVE,,,NANAIMO,BC,V9M 4L6,CA,6.881,="303243479",10.000,9.000,5.130,="1067850", ,="00236500010678506",2P,Prepaid,0001116658,0028,G0592935,
61299A,137559,02,="00000236500192598463",CUSTOMER ABC #28,2222 BETRAL DRIVE,,,NANAIMO,BC,V9M 4L6,CA,7.438,="303243479",10.000,2.250,5.000,="1067850", ,="00236500010678506",2P,Prepaid,0001116658,0028,G0592935,
*A,237624,02,="00000736500192624766",CUSTOMER XYZ #2792,187 MAIN STREET,,PO BOX 269,ATOWN,NS,P0T 1T0,CA,10.368,="303243480",14.750,13.500,4.000,="1067850", ,="00236500010678506",2P,Prepaid,0002223324,2792,G0592935,
 
Last edited:
Upvote 0
MARKG said:
Here's a few lines, Rick; (the end of one Line/record is the comma after "Client Number". That comma represents the 26th segment.

The specific segment we should discuss moving forward is Segment 6 (Address 1 field). Currently it splits 2222 and then places BETRAL DRIVE into Segment 7 variable instead of 2222 BETRAL DRIVE into Segment 6 variable.

Consignee PO#,CCC Order#,CCC WH,Carton ID,Ship To Name,Address 1,Address 2,Address 3,City,State,Zip Code,Country,Weight,Container ID,Length,Width,Height,Trailer #,H File#,C&C BOL#,Carrier,Collect/Prepaid,Billing Acct#,Ship To#,Client Number,
61299A,137559,02,="00000236500192598098",CUSTOMER ABC #28,2222 BETRAL DRIVE,,,NANAIMO,BC,V9M 4L6,CA,6.881,="303243479",10.000,9.000,5.130,="1067850", ,="00236500010678506",2P,Prepaid,0001116658,0028,G0592935,
61299A,137559,02,="00000236500192598463",CUSTOMER ABC #28,2222 BETRAL DRIVE,,,NANAIMO,BC,V9M 4L6,CA,7.438,="303243479",10.000,2.250,5.000,="1067850", ,="00236500010678506",2P,Prepaid,0001116658,0028,G0592935,
*A,237624,02,="00000736500192624766",CUSTOMER XYZ #2792,187 MAIN STREET,,PO BOX 269,ATOWN,NS,P0T 1T0,CA,10.368,="303243480",14.750,13.500,4.000,="1067850", ,="00236500010678506",2P,Prepaid,0002223324,2792,G0592935,
Click to expand...
Hmm, I thought the shape might be more obvious. You say the comma after the words "Client Number" marks the end of the first record, so it looks like there are no natural "new line" markers in the file; rather, it is just a single, very long line of text with comma delimiters (but no Line Feeds or Carriage Returns in it)... does that sound right? Can I assume there are no commas within the quoted text? Or is that a possibility?
 
Upvote 0
Rick Rothstein said:
Hmm, I thought the shape might be more obvious. You say the comma after the words "Client Number" marks the end of the first record, so it looks like there are no natural "new line" markers in the file; rather, it is just a single, very long line of text with comma delimiters (but no Line Feeds or Carriage Returns in it)... does that sound right? Can I assume there are no commas within the quoted text? Or is that a possibility?
Click to expand...

Correct no line feed or carriage returns. Correct, no commas within the quoted text.

If you use this code you'll see what I mean. The Do While Not Loop was just to obtain visibility during my testing phase which shows it splitting out the numeric portion of the Address 1 field.

Dim Seg1, Seg2, Seg3, Seg4, Seg5, Seg6, Seg7, Seg8, Seg9, Seg10, Seg11, Seg12, Seg13, Seg14, Seg15, Seg16, Seg17, Seg18, Seg19, Seg20, Seg21, Seg22, Seg23, Seg24, Seg25, Seg26 As String

Open "C:\...the source data" for Input As #1

Input #1, Seg1, Seg2, Seg3, Seg4, Seg5, Seg6, Seg7, Seg8, Seg9, Seg10, Seg11, Seg12, Seg13, Seg14, Seg15, Seg16, Seg17, Seg18, Seg19, Seg20, Seg21, Seg22, Seg23, Seg24, Seg25, Seg26

Do While Not (EOF(1))
Input #1, Seg1
Debug.Print Seg1
Loop
 
Upvote 0
MARKG said:
Correct no line feed or carriage returns. Correct, no commas within the quoted text.
Click to expand...
See if this code lets you do what you want (you should be able to put your existing Seg# processing code where I have indicated as the code will loop through your data 26 segments at a time)...

Code: 
Sub Segments26()
  Dim X As Long, FileNum As Long, TotalFile As String, IndividualData() As String
  Dim Seg1, Seg2, Seg3, Seg4, Seg5, Seg6, Seg7, Seg8, Seg9, Seg10, Seg11, Seg12, Seg13, Seg14, Seg15, Seg16, Seg17, Seg18, Seg19, Seg20, Seg21, Seg22, Seg23, Seg24, Seg25, Seg26 As String
  FileNum = FreeFile
  Open "C:\Temp\26SegmentTest.txt" For Binary As #FileNum
    TotalFile = Space(LOF(FileNum))
    Get #FileNum, , TotalFile
  Close #FileNum
  On Error Resume Next
  IndividualData = Split(TotalFile, ",")
  For X = 0 To UBound(IndividualData) Step 26
    Seg1 = IndividualData(X)
    Seg2 = IndividualData(X + 1)
    Seg3 = IndividualData(X + 2)
    Seg4 = IndividualData(X + 3)
    Seg5 = IndividualData(X + 4)
    Seg6 = IndividualData(X + 5)
    Seg7 = IndividualData(X + 6)
    Seg8 = IndividualData(X + 7)
    Seg9 = IndividualData(X + 8)
    Seg10 = IndividualData(X + 9)
    Seg11 = IndividualData(X + 10)
    Seg12 = IndividualData(X + 11)
    Seg13 = IndividualData(X + 12)
    Seg14 = IndividualData(X + 13)
    Seg15 = IndividualData(X + 14)
    Seg16 = IndividualData(X + 15)
    Seg17 = IndividualData(X + 16)
    Seg18 = IndividualData(X + 17)
    Seg19 = IndividualData(X + 18)
    Seg20 = IndividualData(X + 19)
    Seg21 = IndividualData(X + 20)
    Seg22 = IndividualData(X + 21)
    Seg23 = IndividualData(X + 22)
    Seg24 = IndividualData(X + 23)
    Seg25 = IndividualData(X + 24)
    Seg26 = IndividualData(X + 25)
    '
    '  Seg1 thru Seg26 should be loaded with the data
    '  you want to process, so put your code here
    '
  Next
End Sub
 
Upvote 0
Rick Rothstein said:
See if this code lets you do what you want (you should be able to put your existing Seg# processing code where I have indicated as the code will loop through your data 26 segments at a time)...

Code: 
Sub Segments26()
  Dim X As Long, FileNum As Long, TotalFile As String, IndividualData() As String
  Dim Seg1, Seg2, Seg3, Seg4, Seg5, Seg6, Seg7, Seg8, Seg9, Seg10, Seg11, Seg12, Seg13, Seg14, Seg15, Seg16, Seg17, Seg18, Seg19, Seg20, Seg21, Seg22, Seg23, Seg24, Seg25, Seg26 As String
  FileNum = FreeFile
  Open "C:\Temp\26SegmentTest.txt" For Binary As #FileNum
    TotalFile = Space(LOF(FileNum))
    Get #FileNum, , TotalFile
  Close #FileNum
  On Error Resume Next
  IndividualData = Split(TotalFile, ",")
  For X = 0 To UBound(IndividualData) Step 26
    Seg1 = IndividualData(X)
    Seg2 = IndividualData(X + 1)
    Seg3 = IndividualData(X + 2)
    Seg4 = IndividualData(X + 3)
    Seg5 = IndividualData(X + 4)
    Seg6 = IndividualData(X + 5)
    Seg7 = IndividualData(X + 6)
    Seg8 = IndividualData(X + 7)
    Seg9 = IndividualData(X + 8)
    Seg10 = IndividualData(X + 9)
    Seg11 = IndividualData(X + 10)
    Seg12 = IndividualData(X + 11)
    Seg13 = IndividualData(X + 12)
    Seg14 = IndividualData(X + 13)
    Seg15 = IndividualData(X + 14)
    Seg16 = IndividualData(X + 15)
    Seg17 = IndividualData(X + 16)
    Seg18 = IndividualData(X + 17)
    Seg19 = IndividualData(X + 18)
    Seg20 = IndividualData(X + 19)
    Seg21 = IndividualData(X + 20)
    Seg22 = IndividualData(X + 21)
    Seg23 = IndividualData(X + 22)
    Seg24 = IndividualData(X + 23)
    Seg25 = IndividualData(X + 24)
    Seg26 = IndividualData(X + 25)
    '
    '  Seg1 thru Seg26 should be loaded with the data
    '  you want to process, so put your code here
    '
  Next
End Sub
Click to expand...
Wow! thank-you for the code, Rick; I will try and test before the end of today. If not, I will for sure get to it first thing tomorrow and will send you an update. So I guess your use of Binary, Split, and Ubound keeps the string in tact between each comma delimeter? I had no idea it had to be that detailed as my code was only tripping up at that address segment.

I wouldn't have been able to construct that myself, so again, I very much appreciate the time you have spent on this today!

Cheers!
Mark
 
Upvote 0
MARKG said:
So I guess your use of Binary, Split, and Ubound keeps the string in tact between each comma delimeter? I had no idea it had to be that detailed as my code was only tripping up at that address segment.
Click to expand...
I haven't worked with disk access data processing in ages, so I don't remember all the quirks about Input#, Write#, Print# and such similar commands. The beauty of the method I posted is it loads the entire document into a single String variable and it does this quite quickly. This allows you to parse the file in memory all at once.... which gives you enormous flexibility. Here, splitting the file on a comma delimiter guarantees everything between commas stays together. As long as your file is less than, say, 30 to 50 Megs in size, doing it this way will be much faster than continually hitting the disk over and over again in order to read the file in dribs and drabs. By the way, I duplicated your Seg1, Seg2, Seg3 variable structure because any existing processing code you have would be built around such a structure, but if I were writing this program, I would use an array in place of all those individual Seg# variables.
 
Upvote 0
Rick Rothstein said:
I haven't worked with disk access data processing in ages, so I don't remember all the quirks about Input#, Write#, Print# and such similar commands. The beauty of the method I posted is it loads the entire document into a single String variable and it does this quite quickly. This allows you to parse the file in memory all at once.... which gives you enormous flexibility. Here, splitting the file on a comma delimiter guarantees everything between commas stays together. As long as your file is less than, say, 30 to 50 Megs in size, doing it this way will be much faster than continually hitting the disk over and over again in order to read the file in dribs and drabs. By the way, I duplicated your Seg1, Seg2, Seg3 variable structure because any existing processing code you have would be built around such a structure, but if I were writing this program, I would use an array in place of all those individual Seg# variables.
Click to expand...


Your code worked perfectly! Thank-you. I incorporated the elements throughout my larger project. Cheers!
Mark
 
Upvote 0
You must log in or register to reply here.

Forum statistics

Threads
1,223,234
Messages
6,170,891
Members
452,366
Latest member
TePunaBloke

Share this page

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top