importing data

[harry]

Hi Folks:
I'm trying to do something that looks simple, but I can't make it
work right.

It's Access 2000 on a Win2000 computer. I create a database with 5
columbs. The data I need to import is text. The way it's formatted
is one line each field, for the 5 fields, then to the next record.
The text file looks like this:

WPTJ410
MG
Active
7/1/2011
10/15/2001 7:54:17 AM
KKC325
IG
Active
4/20/2013
3/1/2003 12:49:12 AM
WPNE621
MG
Active
5/12/2012 1:32:31 PM
4/9/2002 12:51:12 AM
....etc..

This is the first 3 records, 5 fields each. What I'm trying to make
it look like is something like this:
__________________________________________________ ___________________
WPTJ410 | MG | Active | 7/1/2011 | 10/15/2001 7:54:17 AM
KKC325 | IG | Active | 4/20/2013 | 3/1/2003 12:49:12 AM
WPNE621 | MG | Active | 5/12/2012 1:32:31 PM | 4/9/2002 12:51:12 AM

Something like that. I can enter the data by hand, just as it is in
the text file with the returns, and it works fine! Importing always
puts the data down the first columb. So does Copy/Paste. I use to do
this all the time with Microsoft Works..

Any help appreciated. I MUST be missing something. This should be
easy.
Thanks!
Harry

[Filips Benoit]

My first idea is this

Import your data in Excel
Add Column before A to add RowRanking 1 >
Put data in rows: C1=B1 D1=B2 E1=B3 F1=B4 G1=B5 and copy this till the
end of data in columnB
Rows 1, 6, 11 etc has the correct data in rows colmunsC > G
Import Excelsheet in Access
Import the correct rows in your table. Select row 1, 6, 11 . I use a
function in a query

SELECT Sheet1.F3, Sheet1.F4, Sheet1.F5, Sheet1.F6, Sheet1.F7
FROM Sheet1
WHERE (((VijfVoud([Sheet1]![F1]-1))=True));
Public Function VijfVoud(ByVal iGetal As Variant) As Boolean
VijfVoud = False
If iGetal = 0 Then VijfVoud = True: Exit Function
If iGetal Mod 5 = 0 Then VijfVoud = True
End Function

Filip

"harry" <ha********@hotmail.com> wrote in message
news:10*************************@posting.google.co m...

Hi Folks:
I'm trying to do something that looks simple, but I can't make it
work right.

It's Access 2000 on a Win2000 computer. I create a database with 5
columbs. The data I need to import is text. The way it's formatted
is one line each field, for the 5 fields, then to the next record.
The text file looks like this:

WPTJ410
MG
Active
7/1/2011
10/15/2001 7:54:17 AM
KKC325
IG
Active
4/20/2013
3/1/2003 12:49:12 AM
WPNE621
MG
Active
5/12/2012 1:32:31 PM
4/9/2002 12:51:12 AM
...etc..

This is the first 3 records, 5 fields each. What I'm trying to make
it look like is something like this:
__________________________________________________ ___________________
WPTJ410 | MG | Active | 7/1/2011 | 10/15/2001 7:54:17 AM
KKC325 | IG | Active | 4/20/2013 | 3/1/2003 12:49:12 AM
WPNE621 | MG | Active | 5/12/2012 1:32:31 PM | 4/9/2002 12:51:12 AM

Something like that. I can enter the data by hand, just as it is in
the text file with the returns, and it works fine! Importing always
puts the data down the first columb. So does Copy/Paste. I use to do
this all the time with Microsoft Works..

Any help appreciated. I MUST be missing something. This should be
easy.
Thanks!
Harry

[Filips Benoit]

My second idea is this !

This Excel-macro does all the work for you:
Macro expects: data in ColumnA and 5 fields per record, always 5 !!

1. Import your data in Excel
2. Add the marco below
3. Run the marco ( Change the code if FieldCount<>5)
4. Import Excelsheet in Access

***************************************
Sub Columns2Rows()
'
' CopyColumns2Rows Macro
' Macro recorded 30/10/2003 by Filips Benoit
'Declarations
Dim iLastRowNr As Integer
Dim iLoop As Integer
Dim iLoop2 As Integer
Dim iRecordFields As Integer
iRecordFields = 5 'Change for other FieldCount
'1 Block Screenupdating
Application.ScreenUpdating = False
Application.Cursor = xlWait
'2 Find last datarow in column A
Columns("A:A").Select
iLastRowNr = Selection.Cells.Find(What:="*", After:=[A1],
SearchDirection:=xlPrevious).Row
'3 Find and store RecordStartRowNrs
For iLoop = 1 To iLastRowNr Step iRecordFields
For iLoop2 = 1 To iRecordFields
ActiveSheet.Cells(iLoop, iLoop2 + 1).Value =
ActiveSheet.Cells(iLoop + iLoop2 - 1, 1).Value
Next iLoop2
Next
'4 Delete old data
Columns("B:F").Select
Selection.Sort Key1:=Range("B1"), Order1:=xlAscending,
Header:=xlGuess, _
OrderCustom:=1, MatchCase:=False, Orientation:=xlTopToBottom
Columns("A:A").Select
Selection.Delete Shift:=xlToLeft

'5 Update screen
Application.ScreenUpdating = True
Application.Cursor = xlDefault

End Sub
****************************************

"Filips Benoit" <be***********@pandora.be> wrote in message
news:ju***********************@phobos.telenet-ops.be...

My first idea is this

Import your data in Excel
Add Column before A to add RowRanking 1 >
Put data in rows: C1=B1 D1=B2 E1=B3 F1=B4 G1=B5 and copy this till the end of data in columnB
Rows 1, 6, 11 etc has the correct data in rows colmunsC > G
Import Excelsheet in Access
Import the correct rows in your table. Select row 1, 6, 11 . I use a
function in a query

SELECT Sheet1.F3, Sheet1.F4, Sheet1.F5, Sheet1.F6, Sheet1.F7
FROM Sheet1
WHERE (((VijfVoud([Sheet1]![F1]-1))=True));
Public Function VijfVoud(ByVal iGetal As Variant) As Boolean
VijfVoud = False
If iGetal = 0 Then VijfVoud = True: Exit Function
If iGetal Mod 5 = 0 Then VijfVoud = True
End Function

Filip

"harry" <ha********@hotmail.com> wrote in message
news:10*************************@posting.google.co m...

Hi Folks:
I'm trying to do something that looks simple, but I can't make it
work right.

It's Access 2000 on a Win2000 computer. I create a database with 5
columbs. The data I need to import is text. The way it's formatted
is one line each field, for the 5 fields, then to the next record.
The text file looks like this:

WPTJ410
MG
Active
7/1/2011
10/15/2001 7:54:17 AM
KKC325
IG
Active
4/20/2013
3/1/2003 12:49:12 AM
WPNE621
MG
Active
5/12/2012 1:32:31 PM
4/9/2002 12:51:12 AM
...etc..

This is the first 3 records, 5 fields each. What I'm trying to make
it look like is something like this:
__________________________________________________ ___________________
WPTJ410 | MG | Active | 7/1/2011 | 10/15/2001 7:54:17 AM
KKC325 | IG | Active | 4/20/2013 | 3/1/2003 12:49:12 AM
WPNE621 | MG | Active | 5/12/2012 1:32:31 PM | 4/9/2002 12:51:12 AM

Something like that. I can enter the data by hand, just as it is in
the text file with the returns, and it works fine! Importing always
puts the data down the first columb. So does Copy/Paste. I use to do
this all the time with Microsoft Works..

Any help appreciated. I MUST be missing something. This should be
easy.
Thanks!
Harry

[Pieter Linden]

"Filips Benoit" <be***********@pandora.be> wrote in message news:<aJ**********************@phobos.telenet-ops.be>...

My second idea is this !

This Excel-macro does all the work for you:
Macro expects: data in ColumnA and 5 fields per record, always 5 !!

1. Import your data in Excel
2. Add the marco below
3. Run the marco ( Change the code if FieldCount<>5)
4. Import Excelsheet in Access

Certainly it couldn't be that complicated! I was thinking of creating
an array of 5 elements, filling the array by looping through 5 fields
at a time (a single record), and writing the record to the database.
No Excel required.

[Pieter Linden]

I guess I'm missing something too, because it took me forever to get
this to work. And it still needs work - like error trapping and
adding in the OpenFile API from Ken Getz... But this part worked for
me. (Note: yes, the code is awful, but I hear James Fortune just
dying to tear it apart and correct it for ya.)

It really needs that API implemented and then it would be much more
useable - just pass the filename to the subroutine and have at it.

'--BEGIN CRUMMY CODE----
Option Compare Database
Option Explicit

Public Sub ImportWeirdFile()
'reads the first record twice and then starts skipping....

Dim varData(0 To 4) As Variant
Dim intFileNo As Integer
Dim intCounter As Integer
Dim strFileName As String

'use KG's OpenFile API to get the file you want to import...
strFileName = "C:\Documents and Settings\Pieter
Linden\Desktop\ImportMe.txt"

intFileNo = FreeFile
intCounter = 0

Open strFileName For Input As #intFileNo
Do While Not EOF(intFileNo) ' Loop until
end of file.

Line Input #1, varData(intCounter) ' Read line
into variable.
Debug.Print intCounter, varData(intCounter) ' output the
line to the screen

If intCounter = 4 Then 'then the
"record" is full, so write to table.
Call WriteValuesToTable(varData)
'Debug.Print
'Debug.Print "end of record"
'Debug.Print

intCounter = 0 'reset the counter
Else
intCounter = intCounter + 1
End If
Loop
Close #intFileNo
End Sub

Public Sub WriteValuesToTable(varMyData As Variant)
Dim db As DAO.Database
Dim rs As DAO.Recordset
Dim intCounter As Integer

Set db = CurrentDb
Set rs = db.OpenRecordset("tblImportStuff", dbOpenTable,
dbappendonly)
Debug.Print "Call from WriteValuesToTable"

rs.AddNew
For intCounter = 0 To 4
rs.Fields(intCounter) = varMyData(intCounter)
Debug.Print intCounter, varMyData(intCounter)
Next intCounter
'Debug.Print
rs.Update

Set rs = Nothing
Set db = Nothing
End Sub

'--END CRUMMY CODE, BEGIN Snickering...

Pieter

[James Fortune]

pi********@hotmail.com (Pieter Linden) wrote in message news:<bf**************************@posting.google. com>...

I guess I'm missing something too, because it took me forever to get
this to work. And it still needs work - like error trapping and
adding in the OpenFile API from Ken Getz... But this part worked for
me. (Note: yes, the code is awful, but I hear James Fortune just
dying to tear it apart and correct it for ya.)

I resemble that remark :-). I'm sorry about what happened before. I
didn't mean to cause you to lose faith in your programming mojo.

James A. Fortune

[Filips Benoit]

Pieter,

I had the Excel-macro on hand !
AND, most people knows how to insert and run a macro in Excel !

Filip

"Pieter Linden" <pi********@hotmail.com> wrote in message
news:bf**************************@posting.google.c om...

"Filips Benoit" <be***********@pandora.be> wrote in message

news:<aJ**********************@phobos.telenet-ops.be>...

My second idea is this !

This Excel-macro does all the work for you:
Macro expects: data in ColumnA and 5 fields per record, always 5 !!

1. Import your data in Excel
2. Add the marco below
3. Run the marco ( Change the code if FieldCount<>5)
4. Import Excelsheet in Access

Certainly it couldn't be that complicated! I was thinking of creating
an array of 5 elements, filling the array by looping through 5 fields
at a time (a single record), and writing the record to the database.
No Excel required.

[James Fortune]

ha********@hotmail.com (harry) wrote in message news:<10*************************@posting.google.c om>...

Hi Folks:
I'm trying to do something that looks simple, but I can't make it
work right.

It's Access 2000 on a Win2000 computer. I create a database with 5
columbs. The data I need to import is text. The way it's formatted
is one line each field, for the 5 fields, then to the next record.
The text file looks like this:

WPTJ410
MG
Active
7/1/2011
10/15/2001 7:54:17 AM
KKC325
IG
Active
4/20/2013
3/1/2003 12:49:12 AM
WPNE621
MG
Active
5/12/2012 1:32:31 PM
4/9/2002 12:51:12 AM
...etc..

This is the first 3 records, 5 fields each. What I'm trying to make
it look like is something like this:
__________________________________________________ ___________________
WPTJ410 | MG | Active | 7/1/2011 | 10/15/2001 7:54:17 AM
KKC325 | IG | Active | 4/20/2013 | 3/1/2003 12:49:12 AM
WPNE621 | MG | Active | 5/12/2012 1:32:31 PM | 4/9/2002 12:51:12 AM

Something like that. I can enter the data by hand, just as it is in
the text file with the returns, and it works fine! Importing always
puts the data down the first columb. So does Copy/Paste. I use to do
this all the time with Microsoft Works..

Any help appreciated. I MUST be missing something. This should be
easy.
Thanks!
Harry

If you feel like taking a walk on the wild side:

'------------------begin weird.awk----------------
BEGIN {
# first create the line for the field names.
print "\"Field1\"" "," "\"Field2\"" "," "\"Field3\"" ","
"\"Field4\"" "," "\"Field5\"";
}

function deletefirstchar(astr)
{
# delete the first character of the string if the string has any
length
if (length(astr) == 0) {return astr}
astr = substr(astr,2)
return astr
}

function deletelastchar(astr)
{
# delete the last character of the string if the string has any length
if (length(astr) == 0) {return astr}
astr = substr(astr,1,length(astr) - 1)
return astr
}

function trim(myStr)
{
lenStr = length(myStr);
if (lenStr == 0)
{return myStr}
else
{
# Cull initial blanks and tabs
while (substr(myStr,1,1)==" " || substr(myStr,1,1)=="\t") {myStr =
deletefirstchar(myStr)}
if (lenStr==0) {return myStr}
# Cull final blanks
while (substr(myStr,length(myStr),1)==" ") {myStr =
deletelastchar(myStr)}
}
return myStr;
}

{
if (length($0) > 0)
{
{printf "%s" "\"" trim($0) "\""}
if (NR / 5.0 == int(NR / 5))
{printf "\n"}
else {printf "%s" ","}
}
}
END {}
'------------------end weird.awk----------------

data1.txt
WPTJ410
MG
Active
7/1/2011
10/15/2001 7:54:17 AM
KKC325
IG
Active
4/20/2013
3/1/2003 12:49:12 AM
WPNE621
MG
Active
5/12/2012 1:32:31 PM
4/9/2002 12:51:12 AM

Command line: C:\Gawk> gawk -f weird.awk data1.txt > out.csv

out.csv
"Field1","Field2","Field3","Field4","Field5"
"WPTJ410","MG","Active","7/1/2011","10/15/2001 7:54:17 AM"
"KKC325","IG","Active","4/20/2013","3/1/2003 12:49:12 AM"
"WPNE621","MG","Active","5/12/2012 1:32:31 PM","4/9/2002 12:51:12 AM"

out.csv can be imported into Access as a comma delimited file and
making sure that the box is checked for field names on the first line.
If the data has any commas in it, it would be better to create a tab
delimited format. Dates can be imported as strings and changed to
Date/Time format in Access. Note that without trim() and its helper
functions this script would be about 16 lines long. Also note that
all the relevant files must be in C:\Gawk for the command line to work
as shown (gawk.exe, weird.awk and data1.txt).

James A. Fortune

[Pieter Linden]

Here, got my mojo working a bit better... Note: you have to get Ken
Getz's OpenFile API from here:

http://www.mvps.org/access/api/api0001.htm

'---code could really use some error trapping....

Option Compare Database
Option Explicit

'this is on a form... the API stuff is in a public module.

Public Sub ImportWeirdFile()

Dim varData(0 To 4) As Variant
Dim intFileNo As Integer
Dim intCounter As Integer
'These are for KG's OpenFile API call
Dim strFilter As String
Dim strInputFileName As String

'--This line is calling the public stuff from the API module...
strFilter = ahtAddFilterItem(strFilter, "Text Files (*.TXT)",
"*.TXT")
strInputFileName = ahtCommonFileOpenSave( _
Filter:=strFilter, OpenFile:=True, _
DialogTitle:="Please select an input file...", _
Flags:=ahtOFN_HIDEREADONLY)
intFileNo = FreeFile
intCounter = 0

Open strInputFileName For Input As #intFileNo
Do While Not EOF(intFileNo) ' Loop until
end of file.

Line Input #1, varData(intCounter) ' Read line
into variable.
Debug.Print intCounter, varData(intCounter) ' output the
line to the screen

If intCounter = 4 Then 'then the
"record" is full, so write to table.
Call WriteValuesToTable(varData) '--separate
only to help debug this mess!
'Debug.Print
'Debug.Print "end of record"
'Debug.Print

intCounter = 0 'reset the counter
Else
intCounter = intCounter + 1
End If
Loop
Close #intFileNo
End Sub

Public Sub WriteValuesToTable(varMyData As Variant)
Dim db As DAO.Database
Dim rs As DAO.Recordset
Dim intCounter As Integer

Set db = CurrentDb
Set rs = db.OpenRecordset("tblImportStuff", dbOpenTable,
dbappendonly)

rs.AddNew
'--copy the data from the array into the table.
'--I used a zero-based array because the fields collection is
zero-based too...
For intCounter = 0 To 4
rs.Fields(intCounter) = varMyData(intCounter)
Next intCounter

rs.Update
rs.Close

Set rs = Nothing
Set db = Nothing
End Sub