Import Horizontal Text Data, Change to Vertical

  • Thread starter Thread starter Ronster
  • Start date Start date
R

Ronster

I need to import a monthly expense text file into Access with the
following layout:

Field1 - SSN
Field2 - Name
Field3 - Month Ending

Starting at Field4 are 20 sets of expense data that requires 4 fields
each (80 fields total). One row per employee.

FieldXX(1st) - Expense Date
FieldXX(2nd) - Expense Description
FieldXX(3rd) - Expense Code
FieldXX(4th) - Expense Amount

The next set of fields are the same, 20 sets total. No, I can't get
the company to change the setup on this text file. I wish.

I need to convert this horizontal data to vertical. One way that seems
to work is running 20 different queries that always pulls field 1,2 and
3, then the next set of expense data. For example Query1 pulls fields
1,2,3,4,5,6,7, Query2 pulls fields 1,2,3,8,9,10,11. I then APPEND this
data to a predefined TABLE called Expense_DB. I end up with all 20
sets fitting into a 7 field table, which is what I want.

My question is there must be an easier way to do this but I'm a newbie
and can't see any other way to get what I want. Any ideas?
 
Import the data into a work table then use something like the following on a
table built. This code is not fully developed, but it may help you. USE IT
ON A COPY of your data.

Post back if you have problems with it.

Create a Destination table with the following 7 fields
SSN
EmployeeName
MonthEnding
ExpenseDate
ExpenseDescription
ExpenseCode
ExpenseAmount

After saving the code in a module, call the function in the immediate window
or from a code with"
fMakeNormalizedTable "TheImportedTable", "TheDestinationTable", 3,0,0,4,
True

'================= CODE Begins =====================
'Turn non-normalized data (repeating fields) into a normalized table

Public Function fMakeNormalizedTable(strSource, strDestination _
, intCountIdColumns _
, Optional intStartField = 0, Optional intStopField = 0 _
, Optional intGroupSize = 1 _
, Optional tfIncludeNulls As Boolean = False)
'===============================================================================
' Procedure : fMakeNormalizedTable
' DateTime : 5/11/2006 07:39
' Author : John Spencer
' CopyRight: John Spencer
' Purpose : Take a non-normalized table with repeating columns and
normalize
' the table. Source table structure is expected to be one to n identifier
columns
' followed by many repeating columns. For example
' FirstName LastName WorkPhone HomePhone MobilePhone FaxPhone AlternatePhone
' Destination table should already exist and should have a structure similar
to
' the source table. The structure would be something like
' the Identifier fields, a field to hold the source's field name, and a
field to
' hold the data in the repeating fields. For example
' FirstName LastName PhoneType PhoneNumber

'------------------------------------------------------------------------------
' strSource = Name of table with data
' strDestination = Name of destination table
' intCountIdColumns = number of identifier columns
' intStopField = Last Column to be used in building populating destination
table
' intStartField = First repeating column
' intGroupSize = Allows for regular group size (x columns in each group)
' # Gum Sold, Flavor
' # Chiclets, ChicletFlavor; #Wrigley, WrigleyFlavor
' tfIncludeNulls = If True then make records for fields where the value is
null
'===============================================================================

Dim dbAny As DAO.Database
Dim strSqlBase As String, strSql As String, strSQLTarget As String
Dim strBuildTableSQL As String
Dim intLoop As Integer
Dim strFieldName As String
Dim rstAny As DAO.Recordset
Dim intLoop2 As Integer
Dim strAdd As String

Static iErrCount As Integer

On Error GoTo ERROR_fMakeNormalizedTable
'---------------------------------------------------------------
' Future Code Enhancements:
' -- Add ability to skip keyfield column in destination table
' -- add ability to start at any column in source table
'---------------------------------------------------------------
Set dbAny = CurrentDb()

'------------------------------------------------------------------------------
' Determine number of times to loop
'------------------------------------------------------------------------------
iErrCount = 1 'set ierrCount to force stop
If intStopField = 0 Or intStopField >
dbAny.TableDefs(strSource).Fields.Count Then
intStopField = dbAny.TableDefs(strSource).Fields.Count - 1
Else
intStopField = intStopField - 1
End If

If intStartField > intStopField Then
MsgBox "Stop! Start field is after stop field.", , "Please fix"
Exit Function
End If

If intStartField = 0 Or intStartField < intCountIdColumns Then
intStartField = intCountIdColumns
Else
intStartField = intStartField - 1
End If

'Check numbers to make sure they work
If intGroupSize <> 1 Then
If (1 + intStopField - intStartField) Mod intGroupSize <> 0 Then
'adjust intstopfield down
intStopField = intStopField - (1 + intStopField - intStartField)
Mod intGroupSize <> 0
End If
End If

'------------------------------------------------------------------------------
' Get field names in destination Table and build insert statement
'------------------------------------------------------------------------------
iErrCount = 0 'initialize errCount
With dbAny.TableDefs(strDestination) 'if this errors then attempt to
build table

For intLoop = 0 To .Fields.Count - 1
strSQLTarget = strSQLTarget & ", [" & .Fields(intLoop).name & "]"
Next intLoop
End With 'dbAny.TableDefs(strDestination)

strSQLTarget = Mid(strSQLTarget, 3) 'Strip off beginning ", "
strSQLTarget = "INSERT INTO [" & strDestination & "] (" & _
strSQLTarget & ") "


'Build SELECT clause for SELECT query portion of Insert query

'Add Identifier fields
With dbAny.TableDefs(strSource)
If .Fields.Count < intCountIdColumns + 1 Then
MsgBox "Not enough fields in destination table", , "Sorry"
Exit Function
End If

strAdd = vbNullString
For intLoop = 0 To intCountIdColumns - 1
strAdd = strAdd & ", [" & .Fields(intLoop).name & "]"
Next intLoop

strSqlBase = "SELECT " & Mid(strAdd, 3) 'Strip off beginning ", "

'Populate the table
For intLoop = intStartField To intStopField Step intGroupSize
strSql = vbNullString
strAdd = vbNullString
For intLoop2 = 0 To intGroupSize - 1
strFieldName = .Fields(intLoop + intLoop2).name
strAdd = strAdd & ", """ & strFieldName & """, " & _
"[" & strFieldName & "] "

Next intLoop2

strSql = strAdd & " FROM [" & strSource & "] "
strAdd = vbNullString

If tfIncludeNulls = False Then
'Build where clause if nulls are to be excluded
For intLoop2 = 0 To intGroupSize - 1
strFieldName = .Fields(intLoop + intLoop2).name
strAdd = strAdd & "[" & strFieldName & "] is not Null OR "
Next intLoop2
strSql = strSql & " WHERE " & Left(strAdd, Len(strAdd) - 4) 'Strip
off last Or
End If


strSql = strSQLTarget & " " & strSqlBase & " " & strSql

dbAny.Execute strSql, dbFailOnError

Next intLoop

End With

EXIT_fMakeNormalizedTable:
On Error GoTo 0
Exit Function

ERROR_fMakeNormalizedTable:
If Err.Number = 3265 And iErrCount = 0 Then
iErrCount = iErrCount + 1
'------------------------------------------------------------------------------
' Build the destination table based on the source table
'------------------------------------------------------------------------------
'Identifier fields
With dbAny.TableDefs(strSource)
For intLoop = 0 To intCountIdColumns - 1
strBuildTableSQL = strBuildTableSQL & ", " & _
.Fields(intLoop).name & " " &
fGetFieldTypeName(.Fields(intLoop).Type)
Next intLoop

'Repeating value fields
For intLoop = intStartField To intStartField + intGroupSize - 1
strBuildTableSQL = strBuildTableSQL & ", " & _
.Fields(intLoop).name & " Text(64)"

strBuildTableSQL = strBuildTableSQL & ", " & _
.Fields(intLoop).name & "Value " & _
fGetFieldTypeName(.Fields(intStartField).Type)
Next intLoop

strBuildTableSQL = Mid(strBuildTableSQL, 3)

strBuildTableSQL = "Create Table " & strDestination & _
"( " & strBuildTableSQL & ")"
dbAny.Execute strBuildTableSQL, dbFailOnError

End With

dbAny.TableDefs.Refresh
Resume
Else
MsgBox "Error " & Err.Number & vbCrLf & Err.Description & vbCrLf & _
" in procedure fMakeNormalizedTable"
Err.Clear
End If
Stop: Resume 'Debug purposes only. Remove from final code
End Function



Private Function fGetFieldTypeName(fldAnyType) As String
'returns string field type
Dim strAny As String
Select Case fldAnyType
' Case dbBigInt
' strAny = "Big Integer"
Case dbBinary
strAny = "Binary"
Case dbBoolean
strAny = "Boolean"
Case dbByte
strAny = "Byte"
' Case dbChar
' strAny = "Char"
Case dbCurrency
strAny = "Currency"
Case dbDate
strAny = "DateTime"
Case dbDecimal
strAny = "Decimal"
Case dbDouble
strAny = "Double"
Case dbFloat
strAny = "Double"
Case dbGUID
strAny = "GUID"
Case dbInteger
strAny = "Integer"
Case dbLong
strAny = "Long"
' Case dbLongBinary
' strAny = "Long Binary (OLE Object)"
Case dbMemo
strAny = "Memo"
Case dbNumeric
strAny = "Numeric"
Case dbSingle
strAny = "Single"
Case dbText
strAny = "Text"
Case dbTime
strAny = "Time"
' Case dbTimeStamp
' strAny = "Time Stamp"
' Case dbVarBinary
' strAny = "VarBinary"
' Case Else
' strAny = "Unknown Type"
End Select

fGetFieldTypeName = strAny

End Function
 
The above looks like it has possibilities but I can't get it to run. I
tried what you wrote in the code:

fMakeNormalizedTable "Deductions_1", "DeductNormal", 3, 4, 60, 3, True

(I have 3 repeat fields not 4)

Deductions_1 is the import table and DeductNormal is the 7 field table
that is empty.

I keep getting a 3346 error, "Number of Query Values and Destination
fields are not the same." I have 6 fields in my destination table and
the first 6 fields in the my import table are the same. Groups start
repeating at column 4. 4,5,6 then 7,8,9, etc. I tried running the
following but got same eror:

fMakeNormalizedTable "Deductions_1", "DeductNormal", 3, 4, 9, 3, True

Number of Identifier fields - 3
First field to start repeat - 4
Last field to stop repeat - 9
Number of repeat fields - 3

What am I doing wrong?
 
I decided to try a UNION query but I keep getting that SYNTAX ERROR IN
FROM CLAUSE on the following code (this is my first UNION query):

SELECT [SSN], [Last], [PeriodDate], [VendCode], [Type], [Amount]
FROM Deductions_1
UNION ALL
SELECT [SSN], [Last], [PayPeriodDate], [Vendor_Code], [VendorType],
[VendorAmount]
FROM DeductNormal
SELECT [SSN], [Last], [PeriodDate], [VendCode2], [Type2], [Amount2]
FROM Deductions_1
UNION ALL
SELECT [SSN], [Last], [PayPeriodDate], [Vendor_Code], [VendorType],
[VendorAmount]
FROM DeductNormal;

Runs OK if I use only one SELECT. I still need to add another 18
SELECTs to move all the data to my DeductNormal table.

Any ideas on this one?
 
Duane said:
You are missing a UNION ALL in the middle.

--
Duane Hookom
MS Access MVP

Ronster said:
I decided to try a UNION query but I keep getting that SYNTAX ERROR IN
FROM CLAUSE on the following code (this is my first UNION query):

SELECT [SSN], [Last], [PeriodDate], [VendCode], [Type], [Amount]
FROM Deductions_1
UNION ALL
SELECT [SSN], [Last], [PayPeriodDate], [Vendor_Code], [VendorType],
[VendorAmount]
FROM DeductNormal
SELECT [SSN], [Last], [PeriodDate], [VendCode2], [Type2], [Amount2]
FROM Deductions_1
UNION ALL
SELECT [SSN], [Last], [PayPeriodDate], [Vendor_Code], [VendorType],
[VendorAmount]
FROM DeductNormal;

Runs OK if I use only one SELECT. I still need to add another 18
SELECTs to move all the data to my DeductNormal table.

Any ideas on this one?
Click to expand...
Click to expand...

Thank you. Posting corrected code.

SELECT [SSN], [Last], [PeriodDate], [VendCode], [Type], [Amount]
FROM Deductions_1
UNION ALL SELECT [SSN], [Last], [PayPeriodDate], [Vendor_Code],
[VendorType], [VendorAmount]
FROM DeductNormal
UNION ALL SELECT [SSN], [Last], [PeriodDate], [VendCode2], [Type2],
[Amount2]
FROM Deductions_1
UNION ALL SELECT [SSN], [Last], [PayPeriodDate], [Vendor_Code],
[VendorType], [VendorAmount]
FROM DeductNormal;
 
Are we to assume you have your issue resolved?

--
Duane Hookom
MS Access MVP

Ronster said:
Duane said:
You are missing a UNION ALL in the middle.

--
Duane Hookom
MS Access MVP

Ronster said:
I decided to try a UNION query but I keep getting that SYNTAX ERROR IN
FROM CLAUSE on the following code (this is my first UNION query):

SELECT [SSN], [Last], [PeriodDate], [VendCode], [Type], [Amount]
FROM Deductions_1
UNION ALL
SELECT [SSN], [Last], [PayPeriodDate], [Vendor_Code], [VendorType],
[VendorAmount]
FROM DeductNormal
SELECT [SSN], [Last], [PeriodDate], [VendCode2], [Type2], [Amount2]
FROM Deductions_1
UNION ALL
SELECT [SSN], [Last], [PayPeriodDate], [Vendor_Code], [VendorType],
[VendorAmount]
FROM DeductNormal;

Runs OK if I use only one SELECT. I still need to add another 18
SELECTs to move all the data to my DeductNormal table.

Any ideas on this one?
Click to expand...
Click to expand...

Thank you. Posting corrected code.

SELECT [SSN], [Last], [PeriodDate], [VendCode], [Type], [Amount]
FROM Deductions_1
UNION ALL SELECT [SSN], [Last], [PayPeriodDate], [Vendor_Code],
[VendorType], [VendorAmount]
FROM DeductNormal
UNION ALL SELECT [SSN], [Last], [PeriodDate], [VendCode2], [Type2],
[Amount2]
FROM Deductions_1
UNION ALL SELECT [SSN], [Last], [PayPeriodDate], [Vendor_Code],
[VendorType], [VendorAmount]
FROM DeductNormal;
Click to expand...
 
Yes. Works great. Thank you.


Duane said:
Are we to assume you have your issue resolved?

--
Duane Hookom
MS Access MVP

Ronster said:
Duane said:
You are missing a UNION ALL in the middle.

--
Duane Hookom
MS Access MVP

I decided to try a UNION query but I keep getting that SYNTAX ERROR IN
FROM CLAUSE on the following code (this is my first UNION query):

SELECT [SSN], [Last], [PeriodDate], [VendCode], [Type], [Amount]
FROM Deductions_1
UNION ALL
SELECT [SSN], [Last], [PayPeriodDate], [Vendor_Code], [VendorType],
[VendorAmount]
FROM DeductNormal
SELECT [SSN], [Last], [PeriodDate], [VendCode2], [Type2], [Amount2]
FROM Deductions_1
UNION ALL
SELECT [SSN], [Last], [PayPeriodDate], [Vendor_Code], [VendorType],
[VendorAmount]
FROM DeductNormal;

Runs OK if I use only one SELECT. I still need to add another 18
SELECTs to move all the data to my DeductNormal table.

Any ideas on this one?
Click to expand...

Thank you. Posting corrected code.

SELECT [SSN], [Last], [PeriodDate], [VendCode], [Type], [Amount]
FROM Deductions_1
UNION ALL SELECT [SSN], [Last], [PayPeriodDate], [Vendor_Code],
[VendorType], [VendorAmount]
FROM DeductNormal
UNION ALL SELECT [SSN], [Last], [PeriodDate], [VendCode2], [Type2],
[Amount2]
FROM Deductions_1
UNION ALL SELECT [SSN], [Last], [PayPeriodDate], [Vendor_Code],
[VendorType], [VendorAmount]
FROM DeductNormal;
Click to expand...
Click to expand...
 
Back
Top