Excel VBA Strings of Numeric Characters as Numbers and not Strings

In the CSV upload, force that column to be recognised as text.

Hi

if you are using  QueryTables.add you could use

 .TextFileColumnDataTypes = _
        Array(xlTextFormat, xlSkipColumn, xlGeneralFormat)
to determine the type of data

Set shFirstQtr = Workbooks(1).Worksheets(1) 
Set qtQtrResults = shFirstQtr.QueryTables _
        .Add(Connection := "TEXT;C:\My Documents\19980331.txt", _
        Destination := shFirstQtr.Cells(1, 1))
With qtQtrResults
        .TextFileParseType = xlDelimited
        .TextFileTextQualifier = xlTextQualifierDoubleQuote
        .TextFileConsecutiveDelimiter = True
        .TextFileTabDelimiter = True
        .TextFileFixedColumnWidths = Array(5, 4, 5)
        .TextFileColumnDataTypes = _
        Array(xlTextFormat, xlSkipColumn, xlGeneralFormat)
    .Refresh
End With

Select allOpen in new window

Regards

Hi Rob, It's not quite as straightforward as that, each line in the csv file has only one comma at the end so there is only one column(see below).

I have to grab each line as a string and then narrow down to the data I need.


extract from csv file:
Item Selection      :     0940012389                  Cut Tube Q250,
,
Item Warehouse Status         : Active                     Order System             : Planned,
Use Item Ordering Data        : No                    Order Method             : Lot for Lot,
Update Inventory/Order Data   : No                    Order Quantity Increment      :         0.0000 [pcs],
Exclude from Cycle Counting   : No                    Minimum Order Quantity        :         0.0000 [pcs],
Storage Zone             :                       Maximum Order Quantity        :  99999999.0000 [pcs],
Default Location Type for                             Fixed Order Quantity     :         1.0000 [pcs],
Inbound Advice           : Bulk                       Economic Order Quantity       :         1.0000 [pcs],
Unit                : pcs       piece                 Maximum Inventory        :  99999999.0000 [pcs],
Package Definition       :                       Reorder Point            :         0.0000 [pcs],
Inv.Carrying Costs per Year   :       0.0000 [GBP]              Safety Stock             :         0.0000 [pcs],
Order Costs              :       0.0000 [GBP]              Order Interval           :    0.00 Days,
Inventory Valuation Method    : Mov. Aver. Unit Cost (MAUC)          Safety Time              :    0.00 Hours,
Valuation by Wareh. Val. Group: Yes

Try this.

Sub GetData()

Dim FF As Integer
Dim strLine As String
Dim lngNR As Long

FF = FreeFile

Open "C:\temp\28704540.csv" For Input As FF

Do While Not EOF(FF)
    Line Input #FF, strLine
    lngNR = lngNR + 1
    Cells(lngNR, "A") = strLine
Loop
Close

End Sub

Select allOpen in new window

You can parse the entire string with a regular expression object.  Based on what you posted, the following pattern:

(?:^|\n)(?:(\w[^:]+?\S)\s*:\s{1,9}(\w[^,:]*?\S)?\s{8,}(\w[^:,]+?\S)\s*:\s{1,9}(\w[^,:]*?\S)|(\w[^:,]+?\S)\s*:\s{1,9}(\w[^,:]*?\S))(?:,|$|\r)

Select allOpen in new window

parses your data like this:

Match 0 Start(0) Length(68) 

	SubMatch 0: 
	SubMatch 1: 
	SubMatch 2: 
	SubMatch 3: 
	SubMatch 4: Item Selection
	SubMatch 5: 0940012389 Cut Tube Q250

Match 1 Start(72) Length(95) 

	SubMatch 0: Item Warehouse Status
	SubMatch 1: Active
	SubMatch 2: Order System
	SubMatch 3: Planned
	SubMatch 4: 
	SubMatch 5: 

Match 2 Start(168) Length(94) 

	SubMatch 0: Use Item Ordering Data
	SubMatch 1: No
	SubMatch 2: Order Method
	SubMatch 3: Lot for Lot
	SubMatch 4: 
	SubMatch 5: 

Match 3 Start(263) Length(108) 

	SubMatch 0: Update Inventory/Order Data
	SubMatch 1: No
	SubMatch 2: Order Quantity Increment
	SubMatch 3: 0.0000 [pcs]
	SubMatch 4: 
	SubMatch 5: 

Match 4 Start(372) Length(108) 

	SubMatch 0: Exclude from Cycle Counting
	SubMatch 1: No
	SubMatch 2: Minimum Order Quantity
	SubMatch 3: 0.0000 [pcs]
	SubMatch 4: 
	SubMatch 5: 

Match 5 Start(481) Length(103) 

	SubMatch 0: Storage Zone
	SubMatch 1: 
	SubMatch 2: Maximum Order Quantity
	SubMatch 3: 99999999.0000 [pcs]
	SubMatch 4: 
	SubMatch 5: 

Match 6 Start(585) Length(103) 

	SubMatch 0: 
	SubMatch 1: 
	SubMatch 2: 
	SubMatch 3: 
	SubMatch 4: Default Location Type for Fixed Order Quantity
	SubMatch 5: 1.0000 [pcs]

Match 7 Start(689) Length(108) 

	SubMatch 0: Inbound Advice
	SubMatch 1: Bulk
	SubMatch 2: Economic Order Quantity
	SubMatch 3: 1.0000 [pcs]
	SubMatch 4: 
	SubMatch 5: 

Match 8 Start(798) Length(103) 

	SubMatch 0: Unit
	SubMatch 1: pcs piece
	SubMatch 2: Maximum Inventory
	SubMatch 3: 99999999.0000 [pcs]
	SubMatch 4: 
	SubMatch 5: 

Match 9 Start(902) Length(98) 

	SubMatch 0: Package Definition
	SubMatch 1: 
	SubMatch 2: Reorder Point
	SubMatch 3: 0.0000 [pcs]
	SubMatch 4: 
	SubMatch 5: 

Match 10 Start(1001) Length(113) 

	SubMatch 0: Inv.Carrying Costs per Year
	SubMatch 1: 0.0000 [GBP]
	SubMatch 2: Safety Stock
	SubMatch 3: 0.0000 [pcs]
	SubMatch 4: 
	SubMatch 5: 

Match 11 Start(1115) Length(100) 

	SubMatch 0: Order Costs
	SubMatch 1: 0.0000 [GBP]
	SubMatch 2: Order Interval
	SubMatch 3: 0.00 Days
	SubMatch 4: 
	SubMatch 5: 

Match 12 Start(1216) Length(111) 

	SubMatch 0: Inventory Valuation Method
	SubMatch 1: Mov. Aver. Unit Cost (MAUC)
	SubMatch 2: Safety Time
	SubMatch 3: 0.00 Hours
	SubMatch 4: 
	SubMatch 5: 

Match 13 Start(1328) Length(36) 

	SubMatch 0: 
	SubMatch 1: 
	SubMatch 2: 
	SubMatch 3: 
	SubMatch 4: Valuation by Wareh. Val. Group
	SubMatch 5: Yes

Select allOpen in new window

Thank you