Import af csv-filer til Excel med VBA makroer

Det er blevet meget almindeligt, at man kan downloade data som en csv-fil. Det kan fx være fra din netbank eller en tjeneste som Google Analytics. Csv-filer er simpelthen tekstfiler, hvor værdierne er adskilt af et separatortegn.

Det er nemt at åbne en csv-fil i Excel, man dobbeltklikker bare på ikonet. Det er også lige ud ad landevejen at åbne dem med VBA, lige undtaget hvis separatortegnet er semikolon - så kan det være tricky.

Det hænger sammen med, at "csv" står for "comma separated values", og at VBA "tænker" på amerikansk og ikke automatisk bruger de lokale indstillinger. Hvis vi, der bruger komma som decimalseparator, også brugte komma til at adskille værdierne, ville det blive noget rod.

I det følgende viser jeg eksempler på, hvordan man kan importere csv-filer med VBA, og der er også et eksempel på, hvordan man kan parse ("læse") filen med kode i stedet for at bruge Excels indbyggede import.

Makrooptageren fejler

Hvis du gemmer et regneark som csv-fil og efterfølgende åbner filen i Notepad, vil du se, at værdierne er nydeligt adskilt med semikolon.

Hvis du nu slår makrooptageren til og åbner filen, går det som smurt, og alt ser godt ud. Den optagede makro ser sådan ud:

Sub Makro1()
'
' Makro1 Makro
' Makro indspillet 23-11-2013 af Eric Bentzen
'

'
    Workbooks.Open Filename:="C:\csvtest\semikolon-csv.csv"
End Sub

Men hvis du efterfølgende afspiller makroen, opdager du, at hele 1. række står i celle A1 med værdierne adskilt af semikolon, 2. række i celle A2 og så fremdeles. (Makroen virker fint, hvis separatortegnet fx er tabulator.)

Sagen er, at da du gemte som csv, brugte Excel de lokale indstillinger og brugte semikolon som separator, men VBA bruger IKKE de lokale indstillinger, medmindre man tvinger det igennem, og det gør man ved at tilføje ", Local:= True". Sådan:

Sub Makro1()
'
' Makro1 Makro
' Makro indspillet 23-11-2013 af Eric Bentzen
'

'
    Workbooks.Open Filename:= _
    "C:\csvtest\semikolon-csv.csv", Local:=True
End Sub

Sub OpenCsv()
Workbooks.OpenText Filename:= _
" C:\csvtest\semikolon-csv.csv ", Local:=True
End Sub

Da det er en csv-fil, behøver man ikke at fortælle, at det er en fil med separatorer, som hvis det var en "almindelig" tekstfil. I eksemplet herunder åbner vi en semikolon-separeret tekstfil, som ligger i samme mappe som regnearket:

Sub SemicolonAdskilt()
Dim sPath As String
sPath = ThisWorkbook.Path & "\semicolonseparated.txt"
Workbooks.OpenText Filename:= _
sPath, DataType:=xlDelimited, Semicolon:=True, Local:=True
End Sub

Normalt er der ikke "lokal-forvirring", når det drejer sig om almindelige tekstfiler, men jeg har oplevet det af og til (fx med datoformatering), og det kan undgås ved at tilføje ", Local:= True".

Makro til at læse en csv- eller txt-fil

Hvis man af en eller anden grund ikke vil benytte Excels indbyggede importfunktioner, kan man parse ("læse") en csv- eller txt-fil med kode.

Der er ingen grund til at opfinde den dybe tallerken mere end én gang, og koden i det følgende har jeg fundet på nettet, og den fungerer glimrende. Jeg har set den flere steder, og jeg ved ikke, hvem forfatteren er, men han skal have tak.

Du kan kopiere koden og sætte den ind i et VBA-modul ved at markere den med musen, trykke CTRL+C og indsætte med CTRL+V. Læser du dette på en lille skærm, kan nogen af kodelinjerne se "knækkede" ud, men linjeskiftene er OK, når du indsætter i VBA-editoren.

Option Explicit

Sub ImportFile()
Dim sPath As String
'Herunder antages det, at filen, csvtest.csv,
'ligger i samme mappe som regnearket. Vil du
'have noget mere fleksibelt, kan du bruge
'Application.GetOpenFilename til at få en
'dialogboks a la Stifinder, hvor man kan
'vælge filen og få filnavnet returneret.
'Hvordan man gør det, viser jeg på siden
'Hurtig import af tekstfiler.
'Du skal bare erstatte filteret "txt" med "csv".
sPath = ThisWorkbook.Path & "\csvtest.csv"

'Procedurekald. Semikolon sættes som separator,
'og data skal indsættes på fanebladet "Ark2".
'Separator og indsætningsark kunne også være
'indlæst fra regnearket eller en inputbox. Der
'er mange muligheder.
copyDataFromCsvFileToSheet sPath, ";", "Ark2"

End Sub
'**************************************************************
Private Sub copyDataFromCsvFileToSheet(parFileName As String, _
parDelimiter As String, parSheetName As String)

Dim Data As Variant  'Det array som filen indlæses i

'Funktionskald - filen læses og fyldes i arrayet
Data = getDataFromFile(parFileName, parDelimiter)

'Hvis arrayet ikke er tomt, indsættes det i
'regnearket i ét hug.
If Not isArrayEmpty(Data) Then
  'Den følgende kopiering kan man undlade,
  'hvis man vil operere direkte på arrayet.
  With Sheets(parSheetName)
    'Slet evt. gammelt indhold
    .Cells.ClearContents
    'Et område dimensioneres til samme størrelse som arrayet
    'og arrayets værdier indsættes i ét hug.
    .Cells(1, 1).Resize(UBound(Data, 1), UBound(Data, 2)) = Data
  End With
End If

End Sub
'**************************************************************
Public Function isArrayEmpty(parArray As Variant) As Boolean
'Returnerer False hvis det ikke er et array, eller hvis det
'dynamiske array ikke er blevet dimensioneret (ReDim) eller
'er blevet slettet (Erase).

If IsArray(parArray) = False Then isArrayEmpty = True
On Error Resume Next
If UBound(parArray) < LBound(parArray) Then
   isArrayEmpty = True
   Exit Function
Else
   isArrayEmpty = False
End If

End Function
'**************************************************************
Private Function getDataFromFile(parFileName As String, _
parDelimiter As String, _
Optional parExcludeCharacter As String = "") As Variant
'parFileName er den separerede fil (csv, txt ...)
'parDelimiter er separatortegnet, fx semikolon.
'Funktionen returnerer et tomt array, hvis filen
'er tom eller ikke kan åbnes.
'Antal kolonner baseres på linjen med flest kolonner
'og ikke på 1. række.
'parExcludeCharacter: Nogle csv-filer har tekststrenge
'i citationstegn ("ABC"), og hvis parExcludeCharacter = """"
'fjernes citationstegnene.

Dim locLinesList() As Variant 'Array
Dim locData As Variant        'Array
Dim i As Long                 'Tæller
Dim j As Long                 'Tæller
Dim locNumRows As Long        'Antal rækker
Dim locNumCols As Long        'Antal kolonner
Dim fso As Variant            'Filsystemobjekt
Dim ts As Variant             'Fil-variabel
Const REDIM_STEP = 10000      'Konstant

'For at bruge VBA's scripting, skal der vist være en reference
'til Microsoft Scripting Runtime. Referencer vælges under
'Tools i VBA-editorens menu.
Set fso = CreateObject("Scripting.FileSystemObject")

On Error GoTo error_open_file
'Sætter ts = filen
Set ts = fso.OpenTextFile(parFileName)
On Error GoTo unhandled_error

'Initialiserer arrayet
ReDim locLinesList(1 To 1) As Variant
i = 0
'Gennemløber filen, tæller antal linjer
'og finder det største antal kolonner
Do While Not ts.AtEndOfStream
  'Hvis 10000 går op i rækkenummeret,
  'redimensioneres arrayet.
  If i Mod REDIM_STEP = 0 Then
    ReDim Preserve locLinesList _
    (1 To UBound(locLinesList, 1) + REDIM_STEP) As Variant
  End If
  locLinesList(i + 1) = Split(ts.ReadLine, parDelimiter)
  j = UBound(locLinesList(i + 1), 1) 'Antal kolonner i rækken
  'Hvis antallet af kolonner er det hidtil højeste,
  'gemmes det nye antal.
  If locNumCols < j Then locNumCols = j
  i = i + 1
Loop

ts.Close 'Luk filen

locNumRows = i

'Hvis antal rækker er 0
If locNumRows = 0 Then Exit Function

ReDim locData(1 To locNumRows, 1 To locNumCols + 1) As Variant

'Kopierer filen ind i et array
'Hvis parExcludeCharacter er angivet,
'fjernes de (fx citationstegn).
If parExcludeCharacter <> "" Then
  For i = 1 To locNumRows
    For j = 0 To UBound(locLinesList(i), 1)
      If Left(locLinesList(i)(j), 1) = parExcludeCharacter Then
        If Right(locLinesList(i)(j), 1) = parExcludeCharacter Then
          locLinesList(i)(j) = _
          Mid(locLinesList(i)(j), 2, Len(locLinesList(i)(j)) - 2)
        Else
          locLinesList(i)(j) = _
          Right(locLinesList(i)(j), Len(locLinesList(i)(j)) - 1)
        End If
      ElseIf Right(locLinesList(i)(j), 1) = parExcludeCharacter Then
        locLinesList(i)(j) = _
        Left(locLinesList(i)(j), Len(locLinesList(i)(j)) - 1)
      End If
      locData(i, j + 1) = locLinesList(i)(j)
    Next j
  Next i
Else
  For i = 1 To locNumRows
    For j = 0 To UBound(locLinesList(i), 1)
      locData(i, j + 1) = locLinesList(i)(j)
    Next j
  Next i
End If

getDataFromFile = locData

Exit Function

error_open_file:  'Returnerer tom Variant
unhandled_error:  'Returnerer tom Variant

End Function

Sådan. Det kan synes som meget kode for at parse en csv- eller tekstfil, men makroen er i sin helhed meget alsidig og kører faktisk ret hurtigt. Og som jeg skrev tidligere: Hvorfor opfinde den dybe tallerken mere end én gang?