Converting 2 Field Data set (column 1 tag) into a 53 Column database

[Patrick McNally]

I have 2 columns of data, perfectly consistent...

The 1st column is like a tag that states this is field 01 = Name , field 02 = Address , field 03 = City , field 04 = ZIP
the 2nd column is the data that belongs in that field.

Current Data:
01 Patrick
02 Main St
03 Springfield
04 12345
01 Roger
02 Elm St
03 Boston
04 02241
01 Mary

[SIZE=+0]02 Canal St [/SIZE]
03 Waco
04 87645




Desired Excel Spreadsheet 5 Columns :
[SIZE=+0]Record1 Patrick Main St Springfield 12345
Record2 Roger Elm St Boston 02241
Record3 Mary [SIZE=+0]Canal St Waco 87645




[/SIZE][/SIZE]

[SIZE=+0]In this example this should be just 3 Rows with the 4 fields filled in (5 if a record counter is created).[/SIZE]

[SIZE=+0]Current Data: My 2 Columns of data in an Access Table are really 53 rows of data each for (50,000 sets of data) - that makes 2,650,000 records in the DB. Note: It was imported from a Fixed Width TXT File (columns 1 - 2 are the filed, 3 - 50 is the data) so if something can be done for importing into Access into 53 columns - that would work too!

Goal: I need to create a single EXCEL spreadsheet that is 53 Columns Wide and 50,000 rows. If I parsed it straight into Excel would equate to 41 TABS and then I'd need to convert them with Vlookup or Pivot Tables which will not work...

I'm hoping there is a 'script' that I can run to populate the data into Excel as a Matrix / Array??? or that on import into Access it can use the 1st column to place the data in the appropriate column...

[/SIZE]Does anyone know a method to do this using Access / Excel tools ?

 

[DCrake]

You need to go back to the start and import the file as fixed width delimited file and define your spec accordingly. This will them import all the fields into seperate ones. Have you got a sample of the incoming delimited file to look at?

 

[Patrick McNally]

It looks exactly similar to the sample with the first 2 charachters identifying the field. Then colms 3 through 50 are the actual pieces of data. The sets are 53 line each.
I'd like to be able to import and convert, the first instance of 01 indicating record 1, then put that data begenning with 01 into Name, then place next row (starts with 02) into Address, then the next row (starts with 03) into the City field etc...

The data raw looks like (no spaces):
01Patrick
02Main St
03Springfield
0412345
01Roger
02Elm St
03Boston
0402241
01Mary
02Canal St
03Waco
0487645

 

[DCrake]

To do this you will need to use VBA my only concern is the size of the incoming file. What is the ballpark size of the file you are wanting to import? Also what are your vba skills like?

 

[Patrick McNally]

There are 15 files a day, most of the files are about 530,000 lines (10,000 real records) but there is usually at least 1 file a day that is > 50,000 records which is produced in a file that that is 2.8 million records...

My skills are VBA skills are not strong. I am an expert in Excel, I've created many advanced Macros in Excel but created them mostly through "record macro" and then combined / edited them based what I really needed the Macros to do.

Years ago I programmed in DBASE 3+ so I very strong logic skills just not with VBA.

 

[DCrake]

Here is the concept of how I would do it.

Get the name of the file to import
Create recordset base on the table that the data is going into

Open the file using Open File For Input As #1

Loop this file until eof

As we know there are 53 fields per record I would set up a loop from 1 to 53
I would then use the loop number to add the data to the corresponding ordinal number field in the table ussing .AddNew and .Update

Close the txt file

For example

Rs.AddNew
input line "01 Fred"
Rs(1) = Mid(Text,3)

Would be appended to field 1 in the table

input line "02 Jones"
Rs(2) = Mid(Text,3)

Would be appended to field 2 in the table

input line "53 True"
Rs(53) = Mid(Text,3)

Would be appended to field 53 in the table
Rs.Update

Remembering that field 0 is the autonumber field that is self populating

also if certain fields are numeric or dates etc you need to ensure that Access adds them as the right data type.

 

[Patrick McNally]

Thank you. I will give that a whirl.

 

[Patrick McNally]

Solved:
Step 1: Setup Table Structures

TABLE STRUCTURES

Table Name 1: Data_in_2_Columns
Fiels Numbers Fields Names Data Type
Field 1 Tag Text
Field 2 Data Text
Field 3 ID AutoNumber

Table Name 2 Data_in_Database_Columns
Fiels Numbers Fields Names Data Type
Field 1 ID AutoNumber
Field 2 fld1 text
Field 3 fld2 text
Field 4 fld3 text
Field 5 fld4 text
Field 6 fld5 text

Note: Create Columns based on number of columns in the data

Step 2: Import Data into Table 1 (Data_in_2_Columns)

• A Click on Table to make active table
• B File -> Get External Data -> Import
• C for Text Fles select "Files of Type (.csv, .txt etc)"
  • Be sure to make sure (in Advanced area) that the field width is as big as needed, you can set txt to max 255

Step 3: Create Module to Covert to Database Structure

• A Module -> New
• B Name Module "Convert Data to Database"
• C MODULE CODE is rows below from Sub to End Sub
  • Note: Modify code based on number of consistent column in the data

Sub subFix2COLUMNStoDATABASE()
Dim rs1 As ADODB.Recordset
Dim rs2 As ADODB.Recordset
Dim cn As Connection
Dim strsql As String
Dim blnNew As Boolean
Set cn = CurrentProject.Connection
strsql = "SELECT * FROM Data_in_2_Columns order by ID;"
Set rs1 = New ADODB.Recordset
rs1.Open strsql, CurrentProject.Connection

Set rs2 = New ADODB.Recordset
With rs2
.ActiveConnection = CurrentProject.Connection
.Source = "Data_in_Database_Columns"
.LockType = adLockOptimistic
.CursorType = adOpenDynamic
.Open
End With
blnNew = False
Do While Not rs1.EOF
Debug.Print rs1(0)

Select Case Trim(rs1("tag")) 'trim is incase of unintentional leading spaces - if there should be remove the Trim
Case "00" 'Tag Name (in example tags are 00, 01, 02, 03, 04)
'start new record
If blnNew = True Then
rs2.Update
End If
rs2.AddNew
blnNew = True
rs2("fld1") = rs1("data")

Case "01"
rs2("fld2") = rs1("data")

Case "02"
rs2("fld3") = rs1("data")

Case "03"
rs2("fld4") = rs1("data")

Case "04"
rs2("fld5") = rs1("data")

End Select


rs1.MoveNext
Loop

rs1.Close
rs2.Update
rs2.Close
Set rs1 = Nothing
Set rs2 = Nothing

End Sub