How to split excel file by columns from website

Posted on 2014-02-03
Last Modified: 2014-02-12
Want to split large text file into multiple excel spreadsheets by column.

The end goal is to parse the file so it can be uploaded to Access 2013 which limits the number of columns in a table to 250.

I would like to know how to:
1.) use the field layout to turn the text file into a tab separated text file
2.) how to split the file by columns and insert each section of columns into a separate sheet inside the existing workbook.

Question by:dwillpower
  • 3
  • 3
LVL 35

Expert Comment

ID: 39830591
I presume the point of this exercise is to have data you can use for analysis.  Importing all those columns into 4 tables with ~ 250 columns each will simply leave you with a worse nightmare.  I would use OLE automation to open the spreadsheet and column by column, insert rows into an Access table.  I didn't spend a lot of time with it but there are several repeating groups and I would use a separate many-side table for each.  So you would end up with a "main" table and several related tables.  The related tables will each have only 4 columns - UniqueID (autonumber) ParentID (FK to main table), DataPointName, DataPointValue.
LVL 45

Expert Comment

ID: 39831824
I'm running this code in Access, since it seems like Excel isn't necessary for this to import correctly from the text file source.  Before running this code, you should create a table in your Access database with the following fields:
Key: text
Name: text(255)
Value: text(255)
I did not specify any primary key for this table.

You will need to change the path string literals associated with the two constants (cSpec, cData).

Option Explicit

Public Sub Q_28355228()
    Dim oFS As Object
    Dim oTS As Object
    Dim strLine As String
    Dim vSpecs As Variant
    Dim oXL As Object
    Dim owkb As Object
    Dim owks As Object
    Dim rs As Recordset
    Dim strKeys As String
    Dim lngLoop As Long
    Dim vKey As Variant
    Dim oDic As Object
    Dim vItem As Variant
    Const cSpec As String = "C:\Users\Mark\Downloads\10eoderl.xlsx"
    Const cData As String = "C:\Users\Mark\Downloads\10eo.txt"
    Set oXL = CreateObject("excel.application")
    Set owkb = oXL.Workbooks.Open(cSpec)
    Set owks = owkb.Worksheets("EO990_10")
    vSpecs = owks.Range(owks.Range("A5"), owks.Range("A5").End(xlDown).Offset(0, 3))
    Set owks = Nothing
    Set owkb = Nothing
    Set oXL = Nothing
    Set rs = DBEngine(0)(0).OpenRecordset("Q_28355228", dbOpenTable)
    Set oDic = CreateObject("scripting.dictionary")
    Set oFS = CreateObject("scripting.filesystemobject")
    Set oTS = oFS.OpenTextFile(cData)
    Do Until oTS.AtEndOfStream
        strLine = oTS.ReadLine
        For lngLoop = LBound(vSpecs, 1) To UBound(vSpecs, 1)
            oDic(vSpecs(lngLoop, 1)) = Mid$(strLine, vSpecs(lngLoop, 3), vSpecs(lngLoop, 4))
        strKeys = vbNullString
        For Each vKey In Array("ein", "taxpd")
            strKeys = strKeys & oDic(vKey)
        For Each vItem In oDic
                rs!Key = strKeys
                rs!Name = vItem
                rs!Value = Trim(oDic(vItem))

End Sub

Open in new window

Once the data has been imported, you can use a crosstab query to look at the data in columns.  You may use the crosstabs to make virtual tables or populate real tables.
Crosstab Example:
TRANSFORM First(Q_28355228.Value) AS FirstOfValue
SELECT Q_28355228.Key
FROM Q_28355228
WHERE Name in ("name","dba_name","state","zip","pt1_num_vtng_gvrn_bdy_mems","pt1_num_ind_vtng_mems","tot_num_empls",
"rltd_entity","rltd_org_cntrld_entity","trnsfr_to_non_chrtbl_org ","actvs_cndctd_prtshp","sch_o_req","num_with_1096","num_w2g_incld",
GROUP BY Q_28355228.Key
PIVOT Q_28355228.Name;

Open in new window

Note: you can't get all the columns in a single crosstab query result, because 826 is just too many for Access to handle.

Author Comment

ID: 39843397
Thanks! Great!

One problem so far,  

vSpecs = owks.Range(owks.Range("A5"), owks.Range("A5").End(xlDown).Offset(0, 3))

it errors on (xldown) saying it not defined?

Best Practices: Disaster Recovery Testing

Besides backup, any IT division should have a disaster recovery plan. You will find a few tips below relating to the development of such a plan and to what issues one should pay special attention in the course of backup planning.

LVL 45

Expert Comment

ID: 39843644
Oops.  Try this.
vSpecs = owks.Range(owks.Range("A5"), owks.Range("A5").End(xlDown).Offset(0, 3)).Value

Open in new window


Author Comment

ID: 39848760
Still getting the variable not defined xldown.....
LVL 45

Accepted Solution

aikimark earned 500 total points
ID: 39849464
replace xldown with -4121

Author Closing Comment

ID: 39854136

Featured Post

Best Practices: Disaster Recovery Testing

Besides backup, any IT division should have a disaster recovery plan. You will find a few tips below relating to the development of such a plan and to what issues one should pay special attention in the course of backup planning.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Ever wondered why sometimes your SQL Server is slow or unresponsive with connections spiking up but by the time you go in, all is well? The following article will show you how to install and configure a SQL job that will send you email alerts includ…
Phishing attempts can come in all forms, shapes and sizes. No matter how familiar you think you are with them, always remember to take extra precaution when opening an email with attachments or links.
Excel styles will make formatting consistent and let you apply and change formatting faster. In this tutorial, you'll learn how to use Excel's built-in styles, how to modify styles, and how to create your own. You'll also learn how to use your custo…
Many functions in Excel can make decisions. The most simple of these is the IF function: it returns a value depending on whether a condition you describe is true or false. Once you get the hang of using the IF function, you will find it easier to us…

776 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question