Importing CSV formatted reports into array variables

S

skydiver

New Member
Joined
Aug 20, 2008
Messages
23
I have a set of 700+ reports that have been exported from an application as quote/comma delimited CSV files (one for each day of business) that I need to read into Excel and assign each field into an array. I will then need to search for values stored in certain sections of each file and map the data into a formatted worksheet.

Here is a sample of the CSV file:
Code: 
0,Store # 1,,,,,,,,,,,,
0,08/19/2008 --   1:30 PM,,,,,,,,,,,,
0,6.1.16,,,,,,,,,,,,
0,Sales By Revenue Center,,,,,,,,,,,,
0,1/2/2007,,,,,,,,,,,,
0,,,,,,,,,,,,,
0, ,,,,,,,,,,,,
0,NET SALES BY CATEGORY,,,,,,,,,,,,
0,,,,,,,,,,,,,
0,,BANQUETS,DINING ROOM,SHELLFISH BAR,TERRACE BAR,CARRY OUT,TERRACE DINING,SOFT SEATING,BRUNCH,LOWER LOUNGE,LOUNGE,UPPER BAR,Grand Ttl
0,FOOD,0,765.55,0,0,0,0,0,0,230.77,107.55,158.25,1262.12
0,LIQUOR,0,35.48,0,0,0,0,0,0,0,19.5,51.75,106.73
0,BEER,0,0,0,0,0,0,0,0,0,0,72.75,72.75
0,WINE,0,152.48,0,0,0,0,0,0,0,43,158,353.48
0,BANQUET REIMBURSIBLE,0,0,0,0,0,0,0,0,0,0,0,0
0,NA BEVERAGES,0,31.89,0,0,0,0,0,0,20.9,9.45,9.8,72.04
0,NA BREWED BEVERAGE,0,7.5,0,0,0,0,0,0,15,6,0,28.5
0,Gift Certificates,0,0,0,0,0,0,0,0,0,0,0,0
0,BANQUET LABOR,0,0,0,0,0,0,0,0,0,0,0,0
0,H/H FOOD,0,0,0,0,0,0,0,0,0,0,26,26
0, , , , , , , , , , , , ,
0, ,------------,------------,------------,------------,------------,------------,------------,------------,------------,------------,------------,------------
0, , , , , , , , , , , , ,
0,Total :,0,992.9,0,0,0,0,0,0,266.67,185.5,476.55,1921.62
0, , , , , , , , , , , , ,
0, ,,,,,,,,,,,,
0,NUMBER OF GUESTS,,,,,,,,,,,,
0, , , , , , , , , , , , ,
0, ,BANQUETS,DINING ROOM,SHELLFISH BAR,TERRACE BAR,CARRY OUT,TERRACE DINING,SOFT SEATING,BRUNCH,LOWER LOUNGE,LOUNGE,UPPER BAR,Grand Ttl
0,LUNCH,0,14,0,0,0,0,0,0,15,4,0,33
0,Afternoon,0,0,0,0,0,0,0,0,0,0,0,0
0,DINNER,2,25,0,0,0,0,0,0,0,2,0,29
0, , , , , , , , , , , , ,
0, ,------------,------------,------------,------------,------------,------------,------------,------------,------------,------------,------------,------------
0, , , , , , , , , , , , ,
0,Total :,2,39,0,0,0,0,0,0,15,6,0,62
0, , , , , , , , , , , , ,
0, ,,,,,,,,,,,,
0,NUMBER OF CHECKS,,,,,,,,,,,,
0, , , , , , , , , , , , ,
0, ,BANQUETS,DINING ROOM,SHELLFISH BAR,TERRACE BAR,CARRY OUT,TERRACE DINING,SOFT SEATING,BRUNCH,LOWER LOUNGE,LOUNGE,UPPER BAR,Grand Ttl
0,LUNCH,0,7,0,0,0,0,0,0,7,4,7,25
0,Afternoon,0,0,0,0,0,0,0,0,0,0,6,6
0,DINNER,1,7,0,0,0,0,0,0,0,2,8,18
0, , , , , , , , , , , , ,
0, ,------------,------------,------------,------------,------------,------------,------------,------------,------------,------------,------------,------------
0, , , , , , , , , , , , ,
0,Total :,1,14,0,0,0,0,0,0,7,6,21,49
0, , , , , , , , , , , , ,
0, ,,,,,,,,,,,,
0,NET SALES BY DAY PART,,,,,,,,,,,,
0, , , , , , , , , , , , ,
0, ,BANQUETS,DINING ROOM,SHELLFISH BAR,TERRACE BAR,CARRY OUT,TERRACE DINING,SOFT SEATING,BRUNCH,LOWER LOUNGE,LOUNGE,UPPER BAR,Grand Ttl
0,LUNCH,0,249.4,0,0,0,0,0,0,266.67,53.2,62.8,632.07
0,Afternoon,0,0,0,0,0,0,0,0,0,0,135.7,135.7
0,DINNER,0,743.5,0,0,0,0,0,0,0,132.3,278.05,1153.85
0, , , , , , , , , , , , ,
0, ,------------,------------,------------,------------,------------,------------,------------,------------,------------,------------,------------,------------
0, , , , , , , , , , , , ,
0,Total :,0,992.9,0,0,0,0,0,0,266.67,185.5,476.55,1921.62
0, , , , , , , , , , , , ,
0,NET SALES BY DAY PART BY CATEGORY,,,,,,,,,,,,
0, , , , , , , , , , , , ,
0,Net Sales For Revenue Center BANQUETS,,,,,,,,,,,,
0, , , , , , , , , , , , ,
0, , , , , , , , , , , , ,
0, ,LUNCH,Afternoon,DINNER,Grand Ttl, , , , , , , ,
0,FOOD,0,0,0,0, , , , , , , ,
0,LIQUOR,0,0,0,0, , , , , , , ,
0,BEER,0,0,0,0, , , , , , , ,
0,WINE,0,0,0,0, , , , , , , ,
0,BANQUET REIMBURSIBLE,0,0,0,0, , , , , , , ,
0,NA BEVERAGES,0,0,0,0, , , , , , , ,
0,NA BREWED BEVERAGE,0,0,0,0, , , , , , , ,
0,Gift Certificates,0,0,0,0, , , , , , , ,
0,BANQUET LABOR,0,0,0,0, , , , , , , ,
0,H/H FOOD,0,0,0,0, , , , , , , ,
0, , , , , , , , , , , , ,
0, ,------------,------------,------------,------------, , , , , , , ,
0, , , , , , , , , , , , ,
0,Total :,0,0,0,0, , , , , , , ,
0, , , , , , , , , , , , ,
0,Net Sales For Revenue Center DINING ROOM,,,,,,,,,,,,
0, , , , , , , , , , , , ,
0, , , , , , , , , , , , ,
0, ,LUNCH,Afternoon,DINNER,Grand Ttl, , , , , , , ,
0,FOOD,156.2,0,609.35,765.55, , , , , , , ,
0,LIQUOR,0,0,35.48,35.48, , , , , , , ,
0,BEER,0,0,0,0, , , , , , , ,
0,WINE,60,0,92.48,152.48, , , , , , , ,
0,BANQUET REIMBURSIBLE,0,0,0,0, , , , , , , ,
0,NA BEVERAGES,25.7,0,6.19,31.89, , , , , , , ,
0,NA BREWED BEVERAGE,7.5,0,0,7.5, , , , , , , ,
0,Gift Certificates,0,0,0,0, , , , , , , ,
0,BANQUET LABOR,0,0,0,0, , , , , , , ,
0,H/H FOOD,0,0,0,0, , , , , , , ,
0, , , , , , , , , , , , ,
0, ,------------,------------,------------,------------, , , , , , , ,
0, , , , , , , , , , , , ,
0,Total :,249.4,0,743.5,992.9, , , , , , , ,
0, , , , , , , , , , , , ,
0,Net Sales For Revenue Center SHELLFISH BAR,,,,,,,,,,,,
0, , , , , , , , , , , , ,
0, , , , , , , , , , , , ,
0, ,LUNCH,Afternoon,DINNER,Grand Ttl, , , , , , , ,
0,FOOD,0,0,0,0, , , , , , , ,
0,LIQUOR,0,0,0,0, , , , , , , ,
0,BEER,0,0,0,0, , , , , , , ,
0,WINE,0,0,0,0, , , , , , , ,
0,BANQUET REIMBURSIBLE,0,0,0,0, , , , , , , ,
0,NA BEVERAGES,0,0,0,0, , , , , , , ,
0,NA BREWED BEVERAGE,0,0,0,0, , , , , , , ,
0,Gift Certificates,0,0,0,0, , , , , , , ,
0,BANQUET LABOR,0,0,0,0, , , , , , , ,
0,H/H FOOD,0,0,0,0, , , , , , , ,
0, , , , , , , , , , , , ,
0, ,------------,------------,------------,------------, , , , , , , ,
0, , , , , , , , , , , , ,
0,Total :,0,0,0,0, , , , , , , ,
0, , , , , , , , , , , , ,
0,Net Sales For Revenue Center TERRACE BAR,,,,,,,,,,,,
0, , , , , , , , , , , , ,
0, , , , , , , , , , , , ,
0, ,LUNCH,Afternoon,DINNER,Grand Ttl, , , , , , , ,
0,FOOD,0,0,0,0, , , , , , , ,
0,LIQUOR,0,0,0,0, , , , , , , ,
0,BEER,0,0,0,0, , , , , , , ,
0,WINE,0,0,0,0, , , , , , , ,
0,BANQUET REIMBURSIBLE,0,0,0,0, , , , , , , ,
0,NA BEVERAGES,0,0,0,0, , , , , , , ,
0,NA BREWED BEVERAGE,0,0,0,0, , , , , , , ,
0,Gift Certificates,0,0,0,0, , , , , , , ,
0,BANQUET LABOR,0,0,0,0, , , , , , , ,
0,H/H FOOD,0,0,0,0, , , , , , , ,
0, , , , , , , , , , , , ,
0, ,------------,------------,------------,------------, , , , , , , ,
0, , , , , , , , , , , , ,
0,Total :,0,0,0,0, , , , , , , ,
0, , , , , , , , , , , , ,
0,Net Sales For Revenue Center CARRY OUT,,,,,,,,,,,,
0, , , , , , , , , , , , ,
0, , , , , , , , , , , , ,
0, ,LUNCH,Afternoon,DINNER,Grand Ttl, , , , , , , ,
0,FOOD,0,0,0,0, , , , , , , ,
0,LIQUOR,0,0,0,0, , , , , , , ,
0,BEER,0,0,0,0, , , , , , , ,
0,WINE,0,0,0,0, , , , , , , ,
0,BANQUET REIMBURSIBLE,0,0,0,0, , , , , , , ,
0,NA BEVERAGES,0,0,0,0, , , , , , , ,
0,NA BREWED BEVERAGE,0,0,0,0, , , , , , , ,
0,Gift Certificates,0,0,0,0, , , , , , , ,
0,BANQUET LABOR,0,0,0,0, , , , , , , ,
0,H/H FOOD,0,0,0,0, , , , , , , ,
0, , , , , , , , , , , , ,
0, ,------------,------------,------------,------------, , , , , , , ,
0, , , , , , , , , , , , ,
0,Total :,0,0,0,0, , , , , , , ,
0, , , , , , , , , , , , ,
0,Net Sales For Revenue Center TERRACE DINING,,,,,,,,,,,,
0, , , , , , , , , , , , ,
0, , , , , , , , , , , , ,
0, ,LUNCH,Afternoon,DINNER,Grand Ttl, , , , , , , ,
0,FOOD,0,0,0,0, , , , , , , ,
0,LIQUOR,0,0,0,0, , , , , , , ,
0,BEER,0,0,0,0, , , , , , , ,
0,WINE,0,0,0,0, , , , , , , ,
0,BANQUET REIMBURSIBLE,0,0,0,0, , , , , , , ,
0,NA BEVERAGES,0,0,0,0, , , , , , , ,
0,NA BREWED BEVERAGE,0,0,0,0, , , , , , , ,
0,Gift Certificates,0,0,0,0, , , , , , , ,
0,BANQUET LABOR,0,0,0,0, , , , , , , ,
0,H/H FOOD,0,0,0,0, , , , , , , ,
0, , , , , , , , , , , , ,
0, ,------------,------------,------------,------------, , , , , , , ,
0, , , , , , , , , , , , ,
0,Total :,0,0,0,0, , , , , , , ,
0, , , , , , , , , , , , ,
0,Net Sales For Revenue Center SOFT SEATING,,,,,,,,,,,,
0, , , , , , , , , , , , ,
0, , , , , , , , , , , , ,
0, ,LUNCH,Afternoon,DINNER,Grand Ttl, , , , , , , ,
0,FOOD,0,0,0,0, , , , , , , ,
0,LIQUOR,0,0,0,0, , , , , , , ,
0,BEER,0,0,0,0, , , , , , , ,
0,WINE,0,0,0,0, , , , , , , ,
0,BANQUET REIMBURSIBLE,0,0,0,0, , , , , , , ,
0,NA BEVERAGES,0,0,0,0, , , , , , , ,
0,NA BREWED BEVERAGE,0,0,0,0, , , , , , , ,
0,Gift Certificates,0,0,0,0, , , , , , , ,
0,BANQUET LABOR,0,0,0,0, , , , , , , ,
0,H/H FOOD,0,0,0,0, , , , , , , ,
0, , , , , , , , , , , , ,
0, ,------------,------------,------------,------------, , , , , , , ,
0, , , , , , , , , , , , ,
0,Total :,0,0,0,0, , , , , , , ,
0, , , , , , , , , , , , ,
0,Net Sales For Revenue Center BRUNCH,,,,,,,,,,,,
0, , , , , , , , , , , , ,
0, , , , , , , , , , , , ,
0, ,LUNCH,Afternoon,DINNER,Grand Ttl, , , , , , , ,
0,FOOD,0,0,0,0, , , , , , , ,
0,LIQUOR,0,0,0,0, , , , , , , ,
0,BEER,0,0,0,0, , , , , , , ,
0,WINE,0,0,0,0, , , , , , , ,
0,BANQUET REIMBURSIBLE,0,0,0,0, , , , , , , ,
0,NA BEVERAGES,0,0,0,0, , , , , , , ,
0,NA BREWED BEVERAGE,0,0,0,0, , , , , , , ,
0,Gift Certificates,0,0,0,0, , , , , , , ,
0,BANQUET LABOR,0,0,0,0, , , , , , , ,
0,H/H FOOD,0,0,0,0, , , , , , , ,
0, , , , , , , , , , , , ,
0, ,------------,------------,------------,------------, , , , , , , ,
0, , , , , , , , , , , , ,
0,Total :,0,0,0,0, , , , , , , ,
0, , , , , , , , , , , , ,
0,Net Sales For Revenue Center LOWER LOUNGE,,,,,,,,,,,,
0, , , , , , , , , , , , ,
0, , , , , , , , , , , , ,
0, ,LUNCH,Afternoon,DINNER,Grand Ttl, , , , , , , ,
0,FOOD,230.77,0,0,230.77, , , , , , , ,
0,LIQUOR,0,0,0,0, , , , , , , ,
0,BEER,0,0,0,0, , , , , , , ,
0,WINE,0,0,0,0, , , , , , , ,
0,BANQUET REIMBURSIBLE,0,0,0,0, , , , , , , ,
0,NA BEVERAGES,20.9,0,0,20.9, , , , , , , ,
0,NA BREWED BEVERAGE,15,0,0,15, , , , , , , ,
0,Gift Certificates,0,0,0,0, , , , , , , ,
0,BANQUET LABOR,0,0,0,0, , , , , , , ,
0,H/H FOOD,0,0,0,0, , , , , , , ,
0, , , , , , , , , , , , ,
0, ,------------,------------,------------,------------, , , , , , , ,
0, , , , , , , , , , , , ,
0,Total :,266.67,0,0,266.67, , , , , , , ,
0, , , , , , , , , , , , ,
0,Net Sales For Revenue Center LOUNGE,,,,,,,,,,,,
0, , , , , , , , , , , , ,
0, , , , , , , , , , , , ,
0, ,LUNCH,Afternoon,DINNER,Grand Ttl, , , , , , , ,
0,FOOD,43.75,0,63.8,107.55, , , , , , , ,
0,LIQUOR,0,0,19.5,19.5, , , , , , , ,
0,BEER,0,0,0,0, , , , , , , ,
0,WINE,0,0,43,43, , , , , , , ,
0,BANQUET REIMBURSIBLE,0,0,0,0, , , , , , , ,
0,NA BEVERAGES,9.45,0,0,9.45, , , , , , , ,
0,NA BREWED BEVERAGE,0,0,6,6, , , , , , , ,
0,Gift Certificates,0,0,0,0, , , , , , , ,
0,BANQUET LABOR,0,0,0,0, , , , , , , ,
0,H/H FOOD,0,0,0,0, , , , , , , ,
0, , , , , , , , , , , , ,
0, ,------------,------------,------------,------------, , , , , , , ,
0, , , , , , , , , , , , ,
0,Total :,53.2,0,132.3,185.5, , , , , , , ,
0, , , , , , , , , , , , ,
0,Net Sales For Revenue Center UPPER BAR,,,,,,,,,,,,
0, , , , , , , , , , , , ,
0, , , , , , , , , , , , ,
0, ,LUNCH,Afternoon,DINNER,Grand Ttl, , , , , , , ,
0,FOOD,42.8,33.85,81.6,158.25, , , , , , , ,
0,LIQUOR,10,0,41.75,51.75, , , , , , , ,
0,BEER,0,22,50.75,72.75, , , , , , , ,
0,WINE,10,48,100,158, , , , , , , ,
0,BANQUET REIMBURSIBLE,0,0,0,0, , , , , , , ,
0,NA BEVERAGES,0,5.85,3.95,9.8, , , , , , , ,
0,NA BREWED BEVERAGE,0,0,0,0, , , , , , , ,
0,Gift Certificates,0,0,0,0, , , , , , , ,
0,BANQUET LABOR,0,0,0,0, , , , , , , ,
0,H/H FOOD,0,26,0,26, , , , , , , ,
0, , , , , , , , , , , , ,
0, ,------------,------------,------------,------------, , , , , , , ,
0, , , , , , , , , , , , ,
0,Total :,62.8,135.7,278.05,476.55, , , , , , , ,
0,****************************  End of Report  ****************************,,,,,,,,,,,,
This is what I see as the steps of what i am going to need to do to accomplish my task:
Step 1
Start by opening a dialog that prompts for multiple files to be read
Step 2
Read the first file into excel, load the data for this one one sheet into an array.
Step 3
Perform searches on the array to map the data
Step 4
Based on content in the array, loop through it and map the data I need into another array
Step 5
Write the rows of this 3rd array into a worksheet
Step 6 Start over from Step 2 with the next file from step 1


I know that i will be working with at least 3 arrays:
1 for the list of the files selected
1 for the currently processed file
1 for the output array that will be copied into the worksheet

Starting out I need help with Steps 1 & 2: how to select multiple files and load those file names into an array and then how to read the first file and map the contents into a dynamically sized array

Thanks in advanced for the help!
 

Excel Facts

What does custom number format of ;;; mean?
Three semi-colons will hide the value in the cell. Although most people use white font instead.
Sort by date Sort by votes
Step1 & 2
Code: 
Dim fn, e
fn = Application.GetOpenFilename("Text Files (*.txt), *.txt", MultiSelect:=True)
If Not IsArray(fn) Then Exit Sub
For Each e In fn
    Workbooks.Open(e)
    myArray = ActiveWorkbook.Sheets(1).UsedRange.Value
    ' Then what ?
Next
 
Upvote 0
So based on this code, I will actually bring the CSV file into a workseet first then do data extraction to an array variable?
 
Upvote 0
I would prefer not to, I want to select for example 10 files, read the contents of each file one at a time and assign the contents into an array, search for the content that I need to find in that array, place each value that I need and find in the first array into a second array one element at a time then after processing the entire array for the first file in the list, write the values of the 2nd array to a worksheet, clear the 1st array and strat on the next file in the list appending the data from the 2nd array to the worksheet from the first pass.

In this example the array that I ready the raw CSV data into would wind up being 14 columns wide X 272 rows down or dim varFileContents (13, 271).
 
Upvote 0
You must log in or register to reply here.

Similar threads

J
  • Solved
how to lookup one value to set a range for another lookup
2
Replies
14
Views
425
KRice
K
L
  • Question
Dynamic Dropdown List Error
Replies
6
Views
903
Legacy 464248
L

Forum statistics

Threads
1,216,073
Messages
6,128,645
Members
449,461
Latest member
kokoanutt

Share this page

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top