{"id":169,"date":"2013-02-23T22:26:47","date_gmt":"2013-02-23T21:26:47","guid":{"rendered":"http:\/\/mariaevert.dk\/vba\/?p=169"},"modified":"2013-02-23T22:26:47","modified_gmt":"2013-02-23T21:26:47","slug":"vba-import-csv-file","status":"publish","type":"post","link":"https:\/\/mariaevert.dk\/vba\/?p=169","title":{"rendered":"VBA – Import CSV file"},"content":{"rendered":"

This blogpost’s reason d’etre is to show an alternative to the function “Import text” when importing af CSV file into an Excel file. And why not use Import text? Besides having to clean up after the import, the function is quite inefficient and is overkill to use on simple semicolon-separated files. Another reason is that the import function doesn’t handle foreign letters too well. <\/p>\n

But just if anybody’s is wondering: Deleting the data connection and disabling the query definition after data import is done like this: <\/p>\n

\r\n\r\n\r\n    'delete data connection\r\n    ActiveWorkbook.Connections(Filnavn).Delete\r\n    Dim qt As QueryTable\r\n   'delete query connection\r\n   For Each qt In ActiveSheet.QueryTables\r\n            qt.Delete\r\n    Next qt\r\n<\/pre>\n
The alternative is to read the CSV file as a text file, and split each line into an array, and looping through the array to print it in Excel. Credit to Lernkurve from providing the snippet here<\/a>. <\/p>\n
\r\n'This sub only provides the sub ImportCSVfile with parameters\r\nSub InitiateImportCSVFile() \r\nDim filePath As String\r\nDim ImportToRow As Integer\r\nDim StartColumn As Integer\r\n    \r\n    \r\n    filePath = "C:\\Documents and Settings\\MYCSVfile.csv"\r\n    ImportToRow = 1 'the row where it will start printing\r\n    StartColumn = 1 'the start column\r\n    \r\n    ImportCSVFile filePath, ImportToRow, StartColumn \r\nEnd Sub\r\n\r\n'This is the sub that does all the work: \r\n\r\nSub ImportCSVFile(ByVal filePath As String, ByVal ImportToRow As Integer, ByVal StartColumn As Integer)\r\n\r\n    Dim line As String\r\n    Dim arrayOfElements\r\n    Dim element As Variant\r\n\r\n\r\n    Open filePath For Input As #1 ' Open file for input\r\n        Do While Not EOF(1) ' Loop until end of file\r\n            ImportToRow = ImportToRow + 1\r\n            Line Input #1, line\r\n            arrayOfElements = Split(line, ";") 'Split the line into the array.\r\n            \r\n            'Loop thorugh every element in the array and print to Excelfile\r\n            For Each element In arrayOfElements\r\n                Cells(ImportToRow, StartColumn).Value = element\r\n                StartColumn = StartColumn + 1\r\n            Next\r\n        Loop\r\n    Close #1 ' Close file.\r\nEnd Sub\r\n<\/pre>\n
And that’s it!<\/p>\n","protected":false},"excerpt":{"rendered":"
This blogpost’s reason d’etre is to show an alternative to the function “Import text” when importing af CSV file into an Excel file. And why not use Import text? Besides having to clean up after the import, the function is quite inefficient and is overkill to use on simple semicolon-separated files. Another reason is that […]<\/p>\n","protected":false},"author":1,"featured_media":0,"comment_status":"open","ping_status":"open","sticky":false,"template":"","format":"standard","meta":[],"categories":[1],"tags":[],"_links":{"self":[{"href":"https:\/\/mariaevert.dk\/vba\/index.php?rest_route=\/wp\/v2\/posts\/169"}],"collection":[{"href":"https:\/\/mariaevert.dk\/vba\/index.php?rest_route=\/wp\/v2\/posts"}],"about":[{"href":"https:\/\/mariaevert.dk\/vba\/index.php?rest_route=\/wp\/v2\/types\/post"}],"author":[{"embeddable":true,"href":"https:\/\/mariaevert.dk\/vba\/index.php?rest_route=\/wp\/v2\/users\/1"}],"replies":[{"embeddable":true,"href":"https:\/\/mariaevert.dk\/vba\/index.php?rest_route=%2Fwp%2Fv2%2Fcomments&post=169"}],"version-history":[{"count":8,"href":"https:\/\/mariaevert.dk\/vba\/index.php?rest_route=\/wp\/v2\/posts\/169\/revisions"}],"predecessor-version":[{"id":177,"href":"https:\/\/mariaevert.dk\/vba\/index.php?rest_route=\/wp\/v2\/posts\/169\/revisions\/177"}],"wp:attachment":[{"href":"https:\/\/mariaevert.dk\/vba\/index.php?rest_route=%2Fwp%2Fv2%2Fmedia&parent=169"}],"wp:term":[{"taxonomy":"category","embeddable":true,"href":"https:\/\/mariaevert.dk\/vba\/index.php?rest_route=%2Fwp%2Fv2%2Fcategories&post=169"},{"taxonomy":"post_tag","embeddable":true,"href":"https:\/\/mariaevert.dk\/vba\/index.php?rest_route=%2Fwp%2Fv2%2Ftags&post=169"}],"curies":[{"name":"wp","href":"https:\/\/api.w.org\/{rel}","templated":true}]}}