Solved: Import and Consolidate Excel Data

[jmenche]

Hi,

I received an UGLY spreadsheet that I need to import into an Access table. The spreadsheet consists of UPC level dollars and units by store by week There are 29 sheets in the original but I have attached an example with 2 sheets.

Let me count the ways why it's UGLY.
1) The number of rows varies by sheet
2) Dollars and Units, which should be fields, are in rows. #1 compounds this
problem by making the start row between $ and Units vary.

Luckily, the number of columns (weeks) is consistant.

What I need is an access table that has the following fields:
Barcode (UPC), Dept, Store, Week, Dollars, Units

Can someone help??

[Norie]

Do you need information from the header? Does it identify product or something?

If you do the first thing I would probably do is to add that information to each row.

Does every sheet cover the period 2005-03 WK 1 to 2005-10 WK 1?

If that's the case I think the next step would be to consolidate all the sheets in one sheet.

Then import that sheet into Access.

The next step would be to normalize the data, that shouldn't be too much of a problem using code, I've done that before when presented with data in a similar structure.

[chocobochick]

Ouch. You have your work cut out for you, considering the spreedsheet complicates matters by presenting the weeks as separate columns in a crosstab format. Off the top of my head, I can't really think of any way you could reorganize the data properly once you've imported it into Access in its current form.

If you know your way around VB enough, you might consider approaching the problem by writing macros to rewrite the data to a new worksheet in Excel. You could use a couple For..Each loops to write new rows for each columnar week in the range. The real kicker would be lining up the $ and Unit fields in the same row, but you could sort the sheet by Barcode/Dept/Store before running the macro, program the code to verify that these three items are identical in the next row, and test the Metric column with an If..Then statement to determine which row's data goes into which column.

Excel programming isn't exactly my forte, so perhaps someone more qualified than me can hash out the details of that for you if you need it.

[jmenche]

Norie,

I compiled the data and imported into Access. You'll see two tables. The only difference is the 'tblSales-Category' does not have a barcode field (and doesn't need one). I would greatly appreciate your help normalizing these tables.

Thanks!

[Norie]

Is there an attachment somewhere? :)

[jmenche]

grrrr....file size is too big

Norie, can I send it to you another way?

[Norie]

Too big even after being zipped/compacted etc?

Why not just send a few records, that should be enough to illustrate the concept.

[jmenche]

GRRRRRRRRRRR...First, it tells me that a file cannot be larger than 2mb. Now, I zip up a smaller file to 800kb and it tells me that it cannot be larger than 244kb!!

Is there another way to send to you?

[Norie]

Not really, I'm on a dial up connection I'm afraid.

Can you not just attach a few, say 10 records?

You have compacted the database I assume.

[jmenche]

one more time

[Norie]

The attachment worked, I'll download it and have a look tomorrow.

[xCav8r]

:hi:

Normalizing these tables can be done in SQL with UNION queries. I did some of the work for you in the attachment. All you need to do is import your remaining spreadsheets to tblImport, then run qryCreateNormalizedTable which will create tblData, which you can rename to whatever you like. If you want to take the normalization to the next step, you'll obviously need to break out the stores to a new table along the lines of what I did to demo the first sheet--and maybe do something with the dates.

Notes on the attachment:

tblFirstSheet shows the results of running qryCreateNormalizedTable with only the first sheet imported.
qryUnits was generated by the code in the NormalizeUnits procedure in Module1 (shown below).
qrySales was generated by the code in the NormalizeSales procedure in Module1 (shown below). It's virtually identical to qryUnits.
qryCreateNormalizedTable breaks the Store field into two different fields: Store Code and Store Name. The week was left unchanged, and I omitted Barcode and Dept MSC.
Option Compare Database
Option Explicit
Sub NormalizeSales()
Dim db As DAO.Database
Dim intRecordCount As Integer
Dim intCounter As Integer
Dim rstPeriods As DAO.Recordset
Dim strSQL As String
Set db = CurrentDb
Set rstPeriods = db.OpenRecordset("SELECT Week FROM tblWeeks")
With rstPeriods
.MoveLast
intRecordCount = .RecordCount
.MoveFirst
End With
For intCounter = 1 To intRecordCount
Select Case intCounter
Case 1
strSQL = "SELECT tblImport.Store &'|" & rstPeriods.Fields!week _
& "' AS StorePlusPeriod, tblImport.[" & rstPeriods.Fields!week & "] AS Sales" & vbNewLine _
& "FROM tblImport " & vbNewLine _
& "WHERE tblImport.Metric='Sales $'" & vbNewLine & "UNION ALL" & vbNewLine
Case intRecordCount
strSQL = strSQL & "SELECT tblImport.Store & '|" & rstPeriods.Fields!week _
& "', tblImport.[" & rstPeriods.Fields!week & "]" & vbNewLine _
& "FROM tblImport " & vbNewLine _
& "WHERE tblImport.Metric='Sales $'" & vbNewLine & "ORDER BY StorePlusPeriod ASC;"
Case Else
strSQL = strSQL & "SELECT tblImport.Store & '|" & rstPeriods.Fields!week _
& "', tblImport.[" & rstPeriods.Fields!week & "]" & vbNewLine _
& "FROM tblImport " & vbNewLine _
& "WHERE tblImport.Metric='Sales $'" & vbNewLine & "UNION ALL" & vbNewLine
End Select
rstPeriods.MoveNext
Next intCounter
rstPeriods.Close
db.Close
Set rstPeriods = Nothing
Set db = Nothing
Debug.Print strSQL
End Sub
Sub NormalizeUnits()
Dim db As DAO.Database
Dim intRecordCount As Integer
Dim intCounter As Integer
Dim rstPeriods As DAO.Recordset
Dim strSQL As String
Set db = CurrentDb
Set rstPeriods = db.OpenRecordset("SELECT Week FROM tblWeeks")
With rstPeriods
.MoveLast
intRecordCount = .RecordCount
.MoveFirst
End With
For intCounter = 1 To intRecordCount
Select Case intCounter
Case 1
strSQL = "SELECT tblImport.Store &'|" & rstPeriods.Fields!week _
& "' AS StorePlusPeriod, tblImport.[" & rstPeriods.Fields!week & "] AS Units" & vbNewLine _
& "FROM tblImport " & vbNewLine _
& "WHERE tblImport.Metric='Sales Units'" & vbNewLine & "UNION ALL" & vbNewLine
Case intRecordCount
strSQL = strSQL & "SELECT tblImport.Store & '|" & rstPeriods.Fields!week _
& "', tblImport.[" & rstPeriods.Fields!week & "]" & vbNewLine _
& "FROM tblImport " & vbNewLine _
& "WHERE tblImport.Metric='Sales Units'" & vbNewLine & "ORDER BY StorePlusPeriod ASC;"
Case Else
strSQL = strSQL & "SELECT tblImport.Store & '|" & rstPeriods.Fields!week _
& "', tblImport.[" & rstPeriods.Fields!week & "]" & vbNewLine _
& "FROM tblImport " & vbNewLine _
& "WHERE tblImport.Metric='Sales Units'" & vbNewLine & "UNION ALL" & vbNewLine
End Select
rstPeriods.MoveNext
Next intCounter
rstPeriods.Close
db.Close
Set rstPeriods = Nothing
Set db = Nothing
Debug.Print strSQL
End Sub



The first sheet generated 43,232 rows. Access will have a hard time with 28 more sheets. Maybe it would be better to keep it in whatever DB generated the tables in your sheets. :neener:

Okay, I can't attach it because it's about 500k. I stuck it in a temporary spot (http://www.users.qwest.net/~mscavo/jmenche.zip). I'll leave it available at this location for a few days.

[jmenche]

xCav8r,

Thanks for your reply!! I do have a couple of follow ups though.

First, you omitted barcode since it was a repetitive field. However, each one of my remaining sheets is a different barcode so barcode will be critical to the normalization process.

Secondly, I imported all of my data and tested your query and it eventually timed out with an error saying that the temporary disk ran out of space (or something like that). Would putting the union query data into tables first help alleviate this? There are over 1 million records all told.

Thanks again!!

[xCav8r]

First, you omitted barcode since it was a repetitive field. However, each one of my remaining sheets is a different barcode so barcode will be critical to the normalization process.

I added barcode into the mix. I changed the NormalizeSales procedure to accommodate it.

Sub NormalizeSales()
Dim db As DAO.Database
Dim intRecordCount As Integer
Dim intCounter As Integer
Dim rstPeriods As DAO.Recordset
Dim strSQL As String
Set db = CurrentDb
Set rstPeriods = db.OpenRecordset("SELECT Week FROM tblWeeks")
With rstPeriods
.MoveLast
intRecordCount = .RecordCount
.MoveFirst
End With
For intCounter = 1 To intRecordCount
Select Case intCounter
Case 1
strSQL = "SELECT tblImport.Store &'|" & rstPeriods.Fields!week _
& "' AS StorePlusPeriod, tblImport.[" & rstPeriods.Fields!week & "] AS Sales, " _
& "tblImport.Barcode" & vbNewLine _
& "FROM tblImport " & vbNewLine _
& "WHERE tblImport.Metric='Sales $'" & vbNewLine & "UNION ALL" & vbNewLine
Case intRecordCount
strSQL = strSQL & "SELECT tblImport.Store & '|" & rstPeriods.Fields!week _
& "', tblImport.[" & rstPeriods.Fields!week & "], " _
& "tblImport.Barcode" & vbNewLine _
& "FROM tblImport " & vbNewLine _
& "WHERE tblImport.Metric='Sales $'" & vbNewLine & "ORDER BY StorePlusPeriod ASC;"
Case Else
strSQL = strSQL & "SELECT tblImport.Store & '|" & rstPeriods.Fields!week _
& "', tblImport.[" & rstPeriods.Fields!week & "], " _
& "tblImport.Barcode" & vbNewLine _
& "FROM tblImport " & vbNewLine _
& "WHERE tblImport.Metric='Sales $'" & vbNewLine & "UNION ALL" & vbNewLine
End Select
rstPeriods.MoveNext
Next intCounter
rstPeriods.Close
db.Close
Set rstPeriods = Nothing
Set db = Nothing
Debug.Print strSQL
End Sub

It now generates the following SQL which should replace qrySales.


SELECT tblImport.Store &'|2005-03 WK 1' AS StorePlusPeriod, tblImport.[2005-03 WK 1] AS Sales, tblImport.Barcode
FROM tblImport
WHERE tblImport.Metric='Sales $'
UNION ALL
SELECT tblImport.Store & '|2005-03 WK 2', tblImport.[2005-03 WK 2], tblImport.Barcode
FROM tblImport
WHERE tblImport.Metric='Sales $'
UNION ALL
SELECT tblImport.Store & '|2005-03 WK 3', tblImport.[2005-03 WK 3], tblImport.Barcode
FROM tblImport
WHERE tblImport.Metric='Sales $'
UNION ALL
SELECT tblImport.Store & '|2005-03 WK 4', tblImport.[2005-03 WK 4], tblImport.Barcode
FROM tblImport
WHERE tblImport.Metric='Sales $'
UNION ALL
SELECT tblImport.Store & '|2005-03 WK 5', tblImport.[2005-03 WK 5], tblImport.Barcode
FROM tblImport
WHERE tblImport.Metric='Sales $'
UNION ALL
SELECT tblImport.Store & '|2005-04 WK 1', tblImport.[2005-04 WK 1], tblImport.Barcode
FROM tblImport
WHERE tblImport.Metric='Sales $'
UNION ALL
SELECT tblImport.Store & '|2005-04 WK 2', tblImport.[2005-04 WK 2], tblImport.Barcode
FROM tblImport
WHERE tblImport.Metric='Sales $'
UNION ALL
SELECT tblImport.Store & '|2005-04 WK 3', tblImport.[2005-04 WK 3], tblImport.Barcode
FROM tblImport
WHERE tblImport.Metric='Sales $'
UNION ALL
SELECT tblImport.Store & '|2005-04 WK 4', tblImport.[2005-04 WK 4], tblImport.Barcode
FROM tblImport
WHERE tblImport.Metric='Sales $'
UNION ALL
SELECT tblImport.Store & '|2005-05 WK 1', tblImport.[2005-05 WK 1], tblImport.Barcode
FROM tblImport
WHERE tblImport.Metric='Sales $'
UNION ALL
SELECT tblImport.Store & '|2005-05 WK 2', tblImport.[2005-05 WK 2], tblImport.Barcode
FROM tblImport
WHERE tblImport.Metric='Sales $'
UNION ALL
SELECT tblImport.Store & '|2005-05 WK 3', tblImport.[2005-05 WK 3], tblImport.Barcode
FROM tblImport
WHERE tblImport.Metric='Sales $'
UNION ALL
SELECT tblImport.Store & '|2005-05 WK 4', tblImport.[2005-05 WK 4], tblImport.Barcode
FROM tblImport
WHERE tblImport.Metric='Sales $'
UNION ALL
SELECT tblImport.Store & '|2005-06 WK 1', tblImport.[2005-06 WK 1], tblImport.Barcode
FROM tblImport
WHERE tblImport.Metric='Sales $'
UNION ALL
SELECT tblImport.Store & '|2005-06 WK 2', tblImport.[2005-06 WK 2], tblImport.Barcode
FROM tblImport
WHERE tblImport.Metric='Sales $'
UNION ALL
SELECT tblImport.Store & '|2005-06 WK 3', tblImport.[2005-06 WK 3], tblImport.Barcode
FROM tblImport
WHERE tblImport.Metric='Sales $'
UNION ALL
SELECT tblImport.Store & '|2005-06 WK 4', tblImport.[2005-06 WK 4], tblImport.Barcode
FROM tblImport
WHERE tblImport.Metric='Sales $'
UNION ALL
SELECT tblImport.Store & '|2005-06 WK 5', tblImport.[2005-06 WK 5], tblImport.Barcode
FROM tblImport
WHERE tblImport.Metric='Sales $'
UNION ALL
SELECT tblImport.Store & '|2005-07 WK 1', tblImport.[2005-07 WK 1], tblImport.Barcode
FROM tblImport
WHERE tblImport.Metric='Sales $'
UNION ALL
SELECT tblImport.Store & '|2005-07 WK 2', tblImport.[2005-07 WK 2], tblImport.Barcode
FROM tblImport
WHERE tblImport.Metric='Sales $'
UNION ALL
SELECT tblImport.Store & '|2005-07 WK 3', tblImport.[2005-07 WK 3], tblImport.Barcode
FROM tblImport
WHERE tblImport.Metric='Sales $'
UNION ALL
SELECT tblImport.Store & '|2005-07 WK 4', tblImport.[2005-07 WK 4], tblImport.Barcode
FROM tblImport
WHERE tblImport.Metric='Sales $'
UNION ALL
SELECT tblImport.Store & '|2005-08 WK 1', tblImport.[2005-08 WK 1], tblImport.Barcode
FROM tblImport
WHERE tblImport.Metric='Sales $'
UNION ALL
SELECT tblImport.Store & '|2005-08 WK 2', tblImport.[2005-08 WK 2], tblImport.Barcode
FROM tblImport
WHERE tblImport.Metric='Sales $'
UNION ALL
SELECT tblImport.Store & '|2005-08 WK 3', tblImport.[2005-08 WK 3], tblImport.Barcode
FROM tblImport
WHERE tblImport.Metric='Sales $'
UNION ALL
SELECT tblImport.Store & '|2005-08 WK 4', tblImport.[2005-08 WK 4], tblImport.Barcode
FROM tblImport
WHERE tblImport.Metric='Sales $'
UNION ALL
SELECT tblImport.Store & '|2005-09 WK 1', tblImport.[2005-09 WK 1], tblImport.Barcode
FROM tblImport
WHERE tblImport.Metric='Sales $'
UNION ALL
SELECT tblImport.Store & '|2005-09 WK 2', tblImport.[2005-09 WK 2], tblImport.Barcode
FROM tblImport
WHERE tblImport.Metric='Sales $'
UNION ALL
SELECT tblImport.Store & '|2005-09 WK 3', tblImport.[2005-09 WK 3], tblImport.Barcode
FROM tblImport
WHERE tblImport.Metric='Sales $'
UNION ALL
SELECT tblImport.Store & '|2005-09 WK 4', tblImport.[2005-09 WK 4], tblImport.Barcode
FROM tblImport
WHERE tblImport.Metric='Sales $'
UNION ALL
SELECT tblImport.Store & '|2005-09 WK 5', tblImport.[2005-09 WK 5], tblImport.Barcode
FROM tblImport
WHERE tblImport.Metric='Sales $'
UNION ALL
SELECT tblImport.Store & '|2005-10 WK 1', tblImport.[2005-10 WK 1], tblImport.Barcode
FROM tblImport
WHERE tblImport.Metric='Sales $'
ORDER BY StorePlusPeriod ASC;

Create and run a new query with the following SQL. I added barcode as requested, and I also added an index.


CREATE TABLE tblData
(
IndexIt Autoincrement,
[Store Code] Text(5),
[Store Name] Text(100),
Week Text(25),
Units Int,
Sales Int,
BarCode Double
)

Replace the SQL in qryCreateNormalizedTable with this SQL:


INSERT INTO tblData ( [Store Code], [Store Name], Week, Units, Sales, BarCode )
SELECT Left(qryUnits.StorePlusPeriod,5) AS [Store Code], Mid(qryUnits.StorePlusPeriod,7,InStr(1,qryUnits.StorePlusPeriod,"|",1)-7) AS [Store Name],
Mid(qryUnits.StorePlusPeriod,InStr(1,qryUnits.StorePlusPeriod,"|",1)+1) AS Week, qryUnits.Units, qrySales.Sales, qrySales.Barcode
FROM qrySales INNER JOIN qryUnits ON qrySales.StorePlusPeriod=qryUnits.StorePlusPeriod
ORDER BY Left(qryUnits.StorePlusPeriod,5);
This now appends rather than SELECTs INTO.


Secondly, I imported all of my data and tested your query and it eventually timed out with an error saying that the temporary disk ran out of space (or something like that). Would putting the union query data into tables first help alleviate this? There are over 1 million records all told.

I've never tried using Access with tables that have this many rows, so your guess is as good as mine for the best approach to overcoming the limitations with Access. Perhaps some other folks have some advice for you.

As a potential workaround, I added an index and changed the SELECT INTO query to INSERT INTO so that you could append one sheet at a time instead of doing the whole thing at once. Unfortunately, this means re-importing all of your sheets one at a time, running the Append query after each import, and renaming the imported sheet to something other than tblImport to make room for the next sheet.

[jmenche]

cCav8r,


Thanks a lot!! I especially like the union queries. I am much more proficient in queries than programming so your methodology will benefit me in the future as well.

I updated the queries myself and made them tables with make table queries. Everything worked fine and now I have a nice, normalized table. :cloud9:

Thanks again for your help.

Norie,

Thanks for your help too!! You've helped me with a similar problem before.

[Norie]

Glad you got it sorted and sorry I didn't get back to you.:)

I would have used a slightly different approach from xCav8r, but that would probably have taken longer as it would involve looping and recordsets rather than just running queries.