We help IT Professionals succeed at work.

Can't Import .CSV file into Access

Michael Paravicini
on
I am having trouble importing a .csv file into access. The .csv file looks as follows if opened in Excel:


15.02.20      Lohnzahlung                  -70,3      12.02.20
15.02.20      Lohnzahlung                  -74,95      12.02.20
16.02.20      Rechnung Bettio Glarner      -100      30.01.20
20.01.20      Belast. E-Banking      -995.90      20.01.20

The problem seems to be with the coma (as we are using European currency format). I have tried with
OpenText, TransferSpreadSheet and even tried to read line by line using Input As #1. Nothing seems to be working. Any help would really be appreciated. Thanks Michael
Comment
Watch Question

John TsioumprisSoftware & Systems Engineer
CERTIFIED EXPERT
Distinguished Expert 2019

Commented:
Unless you format the .csv to match regional decimal format it won't get imported correctly (hint : Notepad++)
or you use Access to read the .csv as text and import it line by line
Software & Systems Engineer
CERTIFIED EXPERT
Distinguished Expert 2019
Commented:
something like this
Public Function ReadCsv()
Dim StrFile As String
Dim dataline As String
Open PathToYourCsv  For Input As #1    ' Create file name.

 While Not EOF(1)
        Line Input #1, dataline ' read in data 1 line at a time
       Debug.Print dataline
   Wend
Close #1
End Function

Open in new window

Author

Commented:
Thank you so much John. I simply forgot to use Line Input instead of just Input... Now it works perfectly. Again, thank you so much for your help. Best regards Michael

Author

Commented:
Thank you so much John. I tried exactly the same but forgot to use Line Input  instead of just Input.. Now it works perfectly. Thanks for your help. Best regards Michael
John TsioumprisSoftware & Systems Engineer
CERTIFIED EXPERT
Distinguished Expert 2019

Commented:
Glad I helped you