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 8 2550
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********@hot mail.com> wrote in message
news:10******** *************** **@posting.goog le.com... 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
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()
'
' CopyColumns2Row s 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.Scr eenUpdating = False
Application.Cur sor = xlWait
'2 Find last datarow in column A
Columns("A:A"). Select
iLastRowNr = Selection.Cells .Find(What:="*" , After:=[A1],
SearchDirection :=xlPrevious).R ow
'3 Find and store RecordStartRowN rs
For iLoop = 1 To iLastRowNr Step iRecordFields
For iLoop2 = 1 To iRecordFields
ActiveSheet.Cel ls(iLoop, iLoop2 + 1).Value =
ActiveSheet.Cel ls(iLoop + iLoop2 - 1, 1).Value
Next iLoop2
Next
'4 Delete old data
Columns("B:F"). Select
Selection.Sort Key1:=Range("B1 "), Order1:=xlAscen ding,
Header:=xlGuess , _
OrderCustom:=1, MatchCase:=Fals e, Orientation:=xl TopToBottom
Columns("A:A"). Select
Selection.Delet e Shift:=xlToLeft
'5 Update screen
Application.Scr eenUpdating = True
Application.Cur sor = 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********@hot mail.com> wrote in message news:10******** *************** **@posting.goog le.com... 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" <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.
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:\Documen ts 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(intCoun ter) ' Read line
into variable.
Debug.Print intCounter, varData(intCoun ter) ' output the
line to the screen
If intCounter = 4 Then 'then the
"record" is full, so write to table.
Call WriteValuesToTa ble(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 WriteValuesToTa ble(varMyData As Variant)
Dim db As DAO.Database
Dim rs As DAO.Recordset
Dim intCounter As Integer
Set db = CurrentDb
Set rs = db.OpenRecordse t("tblImportStu ff", dbOpenTable,
dbappendonly)
Debug.Print "Call from WriteValuesToTa ble"
rs.AddNew
For intCounter = 0 To 4
rs.Fields(intCo unter) = varMyData(intCo unter)
Debug.Print intCounter, varMyData(intCo unter)
Next intCounter
'Debug.Print
rs.Update
Set rs = Nothing
Set db = Nothing
End Sub
'--END CRUMMY CODE, BEGIN Snickering...
Pieter pi********@hotm ail.com (Pieter Linden) wrote in message news:<bf******* *************** ****@posting.go ogle.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
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********@hot mail.com> wrote in message
news:bf******** *************** ***@posting.goo gle.com... "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. ha********@hotm ail.com (harry) wrote in message news:<10******* *************** ***@posting.goo gle.com>... 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,l ength(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,l ength(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","Field 2","Field3","Fi eld4","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
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 strInputFileNam e As String
'--This line is calling the public stuff from the API module...
strFilter = ahtAddFilterIte m(strFilter, "Text Files (*.TXT)",
"*.TXT")
strInputFileNam e = ahtCommonFileOp enSave( _
Filter:=strFilt er, OpenFile:=True, _
DialogTitle:="P lease select an input file...", _
Flags:=ahtOFN_H IDEREADONLY)
intFileNo = FreeFile
intCounter = 0
Open strInputFileNam e For Input As #intFileNo
Do While Not EOF(intFileNo) ' Loop until
end of file.
Line Input #1, varData(intCoun ter) ' Read line
into variable.
Debug.Print intCounter, varData(intCoun ter) ' output the
line to the screen
If intCounter = 4 Then 'then the
"record" is full, so write to table.
Call WriteValuesToTa ble(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 WriteValuesToTa ble(varMyData As Variant)
Dim db As DAO.Database
Dim rs As DAO.Recordset
Dim intCounter As Integer
Set db = CurrentDb
Set rs = db.OpenRecordse t("tblImportStu ff", 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(intCo unter) = varMyData(intCo unter)
Next intCounter
rs.Update
rs.Close
Set rs = Nothing
Set db = Nothing
End Sub This thread has been closed and replies have been disabled. Please start a new discussion. Similar topics |
by: steve |
last post by:
Hi, I have researched but have not found a good solution to this
problem.
I am importing large amounts of data (over 50 Meg) into a new mysql db
that I set up. I use
>mysql dbname < importfile.txt
But I keep getting timeouts and errors due to the data being too
large. I know that since if I break the imported data into multiple
chuncks (by importing a few tables at a time) then everything works.
|
by: Grim Reaper |
last post by:
I am importing a .csv file into Access that has 37 fields. My problem is
that sometimes the last field only has data at the end of the column (it
looks like when you import a file into Access, for the last field, it
only checks the top few 'cells' to see if there is any data, if not, the
field is not imported).
How do I 'force' Access to import the field, regardless if there is data
in the top of the field or not? For instance, I might...
|
by: sparks |
last post by:
I have never done this and wanted to ask people who have what is the
best way.
One person said import it to excel, then import it into access table.
but since this will be done a lot, I am trying to avoid this extra
step.
can access read in this file directly into a table as append data?
thank you very much for any pointers
|
by: Darren |
last post by:
I have been attempting to create a reservation planning form in excel
that imports Data from an Access database and inserts that information
automaticly into the correct spreed sheet and the correct cells within
that worksheet. The Excel Book is made up of 12 worksheets named
Jan-Dec. Each worksheet has columns labeled as each day of that month.
Column 'A' is reserved for 19 rows named "room1 - room19". The data I
am importing from the...
|
by: nutthatch |
last post by:
I want to be able to import an Excel spreadsheet into Access 2K using
the macro command Transferspreadsheet.
However, the file I am importing (over which I have no control)
contains some records that are "dirty" i.e. the field contents do not
comply with the expected format (date/time) and they end up in a
seperate table of import errors. (The records in "error" are actually
empty fields.)
This is a regular event and I do not want to...
| |
by: Timothy Shih |
last post by:
Hi, I am trying to figure out how to use unmanaged code using P/Invoke. I
wrote a simple function which takes in 2 buffers (one a byte buffer, one a
char buffer) and copies the contents of the byte buffer into the character
pointer. The code looks like the following:
#include <stdio.h>
#include <stdlib.h>
#include "stdafx.h"
BOOL APIENTRY DllMain( HANDLE hModule,
DWORD ul_reason_for_call,
|
by: Mike Collins |
last post by:
I am importing a XML file and have not been having the best of luck in doing
this, but I do have the following solution below. I will not be importing
more than 2000 records at a time, but will be importing data to many
different tables. Can someone explain if there is a better way to do what I
am doing without having to call a stored procedure for each row (like I'm
doing below)? A good example would be great as my understanding of xml or...
|
by: Mike Collins |
last post by:
I am importing a XML file and have not been having the best of luck in doing
this, but I do have the following solution below. I will not be importing
more than 2000 records at a time, but will be importing data to many
different tables. Can someone explain if there is a better way to do what I
am doing without having to call a stored procedure for each row (like I'm
doing below)? A good example would be great as my understanding of xml or...
|
by: hharriel |
last post by:
Hi,
I am hoping someone can help me with an issue I am having with excel
and ms access. I have collected data (which are in individual excel
files) from 49 different school districts. All districts have used the
same excel template and populated the same 32 data fields (columns). I
created one large excel file from all 49 files which gives me a master
table of 60,000 or so records. I have tried to import this master
table into access...
|
by: JMO |
last post by:
I can import a csv file with no problem. I can also add columns to the
datagrid upon import. I want to be able to start importing at the 3rd
row. This will pick up the headers necessary for the datagrid. Once I
can get to that point I need some way to be able to add new data only
to the new columns that were added.
Here is some of my code:
//Function For Importing Data From CSV File
public DataSet ConnectCSV(string filetable)
|
by: marktang |
last post by:
ONU (Optical Network Unit) is one of the key components for providing high-speed Internet services. Its primary function is to act as an endpoint device located at the user's premises. However, people are often confused as to whether an ONU can Work As a Router. In this blog post, we’ll explore What is ONU, What Is Router, ONU & Router’s main usage, and What is the difference between ONU and Router. Let’s take a closer look !
Part I. Meaning of...
| |
by: Hystou |
last post by:
Most computers default to English, but sometimes we require a different language, especially when relocating. Forgot to request a specific language before your computer shipped? No problem! You can effortlessly switch the default language on Windows 10 without reinstalling. I'll walk you through it.
First, let's disable language synchronization. With a Microsoft account, language settings sync across devices. To prevent any complications,...
|
by: Oralloy |
last post by:
Hello folks,
I am unable to find appropriate documentation on the type promotion of bit-fields when using the generalised comparison operator "<=>".
The problem is that using the GNU compilers, it seems that the internal comparison operator "<=>" tries to promote arguments from unsigned to signed.
This is as boiled down as I can make it.
Here is my compilation command:
g++-12 -std=c++20 -Wnarrowing bit_field.cpp
Here is the code in...
|
by: Hystou |
last post by:
Overview:
Windows 11 and 10 have less user interface control over operating system update behaviour than previous versions of Windows. In Windows 11 and 10, there is no way to turn off the Windows Update option using the Control Panel or Settings app; it automatically checks for updates and installs any it finds, whether you like it or not. For most users, this new feature is actually very convenient. If you want to control the update process,...
|
by: agi2029 |
last post by:
Let's talk about the concept of autonomous AI software engineers and no-code agents. These AIs are designed to manage the entire lifecycle of a software development project—planning, coding, testing, and deployment—without human intervention. Imagine an AI that can take a project description, break it down, write the code, debug it, and then launch it, all on its own....
Now, this would greatly impact the work of software developers. The idea...
|
by: isladogs |
last post by:
The next Access Europe User Group meeting will be on Wednesday 1 May 2024 starting at 18:00 UK time (6PM UTC+1) and finishing by 19:30 (7.30PM).
In this session, we are pleased to welcome a new presenter, Adolph Dupré who will be discussing some powerful techniques for using class modules.
He will explain when you may want to use classes instead of User Defined Types (UDT). For example, to manage the data in unbound forms.
Adolph will...
|
by: adsilva |
last post by:
A Windows Forms form does not have the event Unload, like VB6. What one acts like?
| |
by: muto222 |
last post by:
How can i add a mobile payment intergratation into php mysql website.
|
by: bsmnconsultancy |
last post by:
In today's digital era, a well-designed website is crucial for businesses looking to succeed. Whether you're a small business owner or a large corporation in Toronto, having a strong online presence can significantly impact your brand's success. BSMN Consultancy, a leader in Website Development in Toronto offers valuable insights into creating effective websites that not only look great but also perform exceptionally well. In this comprehensive...
| |