NumberFormat

[Freshman]

Hi all,

I have a csv file that I download and then import into an Access db.
There are around 100,000 rows
One of the columns contains a serial number which could look like a number or text. Some containing only number and others a fix of letters, numbers and even symbols.
After importing it I have problems with some values failing (not showing or showing as huge number with 'to the power' at the end)
I played around with converting it to xlsx and also with using NumberFormat = "@" and all other options like ";;" or "#######" or "000000" and the rest.
With some options some of the values are correct but others not and visa versa. I thought the text option "@" would cover all bases but it is not.
After a few hours of trying all sorts I noticed that the sure one that succeeds is if I put a single quote ' in front of all the values in the column before importing it.
I do the above before importing it using docmd.tranferspreadsheet

I tried this in code but it take forever. Not practical

Public Function Test()
'On Error GoTo Err_FixLN
Dim wb As Object
Dim xlApp As Object
Dim rng As Range

Set xlApp = CreateObject("Excel.Application")
''xlApp.Visible = True
Set wb = xlApp.Workbooks.Open("C:\CL.xlsx", True, False)

Set rng = wb.ActiveSheet.Range("B:B")
totrows = rng.rows.Count()

For n = 2 To totrows
    rng.Cells(n, 1).Value = "'" & rng.Cells(n, 1).Value
Next n

wb.Save
wb.Close
xlApp.Quit
MsgBox "Done"

Exit Function
Err_FixLN:
    MsgBox "Error", vbInformation, "Notice"
End Function

I'm open to idea. Maybe a replace function or concat or some idea that will do it faster.
Or some tips on why I can't seem to get a good one-fit-for-all NumberFormat option.

Thanks
FM

 

[arnelgp]

what if you modify your function:

'modified by arnelgp
Public Function Test()
'On Error GoTo Err_FixLN
Dim wb As Object
Dim xlApp As Object
Dim ws As Object
Dim rng As Object
Dim lastrow As Long
Dim arr() As Variant
Dim i As Long
Set xlApp = CreateObject("Excel.Application")
''xlApp.Visible = True
Set wb = xlApp.workbooks.Open("C:\CL.xlsx", True, False)
Set ws = wb.sheets(1)

With ws
    lastrow = .cells(.rows.Count, 1).End(-4162).row
   
    arr = .range("A2:A" & lastrow).value2
    For i = 1 To UBound(arr)
        'Debug.Print arr(i, 1)
        arr(i, 1) = "'" & arr(i, 1)
    Next
   
    .range("A2:A" & lastrow).value2 = arr
End With
   
'
'Set rng = wb.ActiveSheet.range("B:B")
'totrows = rng.rows.Count()
'
'For n = 2 To totrows
'    rng.cells(n, 1).Value = "'" & rng.cells(n, 1).Value
'Next n
'
wb.Save
wb.Close
xlApp.Quit
Set xlApp = Nothing
MsgBox "Done"

Exit Function
Err_FixLN:
    MsgBox "Error", vbInformation, "Notice"
End Function

 

[Freshman]

arnelgp - "you are the man". Cutting the time from forever to about 6 seconds. This will work. Thanks a lot.​

I'm still surprised that the FormatNumber idea could not work for text. Anyway maybe one day I will know why

 

[CJ_London]

Assuming your csv file has headers, import with headers set to false - result will be the header row is imported. This will default all values to text, You can then delete the header record and convert to the appropriate datatype with a query.

I use a query something like this which does everything in one pass and does not require a temporary table or staging area

INSERT INTO desttbl (fldname1, fldname2...)
SELECT cLng(F1), cDate(F2).....
FROM
(SELECT * FROM [TEXT;DATABASE=C:\filePath;HDR=No].filename.csv)  AS txt
WHERE txt.F1<>"colName"

'colName' is the name of the first column

 

[Freshman]

Very interesting idea. Thanks I will also try that as an option. For now I'm using arnelgp suggestion.

 

[CJ_London]

you can also look at using transfertext rather than transferspreadsheet - you can specify the column types and create an import spec for future use

 

[Freshman]

Thanks for the reminder