0

I currently have a large CSV file which data I want to import into an excel sheets. The way I was taught in a class to import was like this:

Dim tSouthStore1 As String
Dim tSouthStore2 As String
Dim tSouthStore3 As String
Dim tNorthStore1 As String
Dim tNorthStore2 As String
Dim sSouthGoats1 As Single
Dim sSouthGoats2 As Single
Dim sSouthGoats3 As Single
Dim sNorthGoats1 As Single
Dim sNorthGoats2 As Single
Dim sSouthChickens1 As Single
Dim sSouthChickens2 As Single
Dim sSouthChickens3 As Single
Dim sNorthChickens1 As Single
Dim sNorthChickens2 As Single

Open ThisWorkbook.Path & "\" & Trim(tSouthFile) For Input As #1
Open ThisWorkbook.Path & "\" & Trim(tNorthFile) For Input As #2

Input #1, tSouthStore1, sSouthGoats1, sSouthChickens1
Input #1, tSouthStore2, sSouthGoats2, sSouthChickens2
Input #1, tSouthStore3, sSouthGoats3, sSouthChickens3
Input #2, tNorthStore1, sNorthGoats1, sNorthChickens1
Input #2, tNorthStore2, sNorthGoats2, sNorthChickens2

Close #1
Close #2

Please tell me there is a better way to do this. The file I'm now working with is near 10,000 rows and 20-some columns. Also this is something I need to be doing a couple times a week, and the data sheets vary in size. How do I handle this?

NWL
  • 11
  • 3
  • Open the CSV file(s) in Excel using `workbooks.open`, and transfer the data from the opened workbook(s). No need to do any parsing in VBA. – Tim Williams Mar 12 '21 at 18:45
  • @TimWilliams sorry, can you elaborate? How do I transfer the data without all the variables and input lines? – NWL Mar 12 '21 at 18:52
  • Does this answer your question? [Is there a way to import data from .csv to active excel sheet?](https://stackoverflow.com/questions/12197274/is-there-a-way-to-import-data-from-csv-to-active-excel-sheet) – Warcupine Mar 12 '21 at 18:55
  • It's difficult to make suggestions without the bigger context around what you actually need to do with the data once you've read it from the file, but once you have the files open in Excel you have "random access" to all of the contained data - you can access any value in the data by row/column index. – Tim Williams Mar 12 '21 at 19:00
  • Does your csv file contain dates or text with leading zeros like `00000123` that you want to remain as text ? – CDP1802 Mar 12 '21 at 19:02
  • @TimWilliams Once I get the data into the excel file, I am mainly just formatting it. I have that part of the macro built. It cleans some of the data by removing extra spaces and fixing renaming a few things. It also changes the formatting of all dates from the csv into short dates. The goal of the whole program is to take data from the CSV, clean and properly format it, then output that data into 2 different CSVs – NWL Mar 12 '21 at 19:10
  • @CDP1802 doesnt contain leading 0's. But after the import one of the things my macro will do is add a leading 0 in one column then it outputs all the data into a new CSV. Is that not going to work? – NWL Mar 12 '21 at 19:12
  • If your csv file has commas as field separator and _no commas within the field values_ then you could process each line in the file relative easily using SPLIT and JOIN without actually importing it into the worksheet . As Tim says it all depends on context. – CDP1802 Mar 13 '21 at 10:01

0 Answers0