populating an Access table from a text file

A text file has data in a format like the below:

Title1
Cell 1 data
Title 2
Cell 2 data
Title 3
Cell 3 data
..
..
..
Title 10 Cell 10 data

Title 11
Cell 11 data
..
..
..
Title 15 Cell 15 data
..
..
..
Title N
Cell N data

I guess I can read this using some VBA code posted at
http://www.mvps.org/access/modules/mdl0057.htm and
http://www.granite.ab.ca/access/readtextfile.htm, add an Instr
function to it so that when I read the text file, I know when to
insert the data properly in the table.

1. Is there another way I can directly import the data into an Access
2007 table without VBA code? I know Access allows import from data,
but my data is not as formatted as I want so the data might end up
being in the table in a way such as

Field 1 of Access Table

Title 1
Cell 1
Title 2
Cell 2
..
..
..
Title N
Cell N

whereas I want it to be like

Field 1                Field 2              Field
3...................Field N of the Access table
Cell 1 data          Cell 2 data        Cell 3 data


2. If I have to use VBA, are the methods I linked to proper(Am i going
in the right direction) and advisable or are there better/easier ways
to do what I want to achieve.

Any advice would be welcome.
0
s
6/4/2010 1:24:14 AM
access 16762 articles. 2 followers. Follow

4 Replies
1323 Views

Similar Articles

[PageSpeed] 59

If you could post some actual data with puncuation someone might have an idea 
on how to proceed.

-- 
Build a little, test a little.


"s" wrote:

> A text file has data in a format like the below:
> 
> Title1
> Cell 1 data
> Title 2
> Cell 2 data
> Title 3
> Cell 3 data
> ..
> ..
> ..
> Title 10 Cell 10 data
> 
> Title 11
> Cell 11 data
> ..
> ..
> ..
> Title 15 Cell 15 data
> ..
> ..
> ..
> Title N
> Cell N data
> 
> I guess I can read this using some VBA code posted at
> http://www.mvps.org/access/modules/mdl0057.htm and
> http://www.granite.ab.ca/access/readtextfile.htm, add an Instr
> function to it so that when I read the text file, I know when to
> insert the data properly in the table.
> 
> 1. Is there another way I can directly import the data into an Access
> 2007 table without VBA code? I know Access allows import from data,
> but my data is not as formatted as I want so the data might end up
> being in the table in a way such as
> 
> Field 1 of Access Table
> 
> Title 1
> Cell 1
> Title 2
> Cell 2
> ..
> ..
> ..
> Title N
> Cell N
> 
> whereas I want it to be like
> 
> Field 1                Field 2              Field
> 3...................Field N of the Access table
> Cell 1 data          Cell 2 data        Cell 3 data
> 
> 
> 2. If I have to use VBA, are the methods I linked to proper(Am i going
> in the right direction) and advisable or are there better/easier ways
> to do what I want to achieve.
> 
> Any advice would be welcome.
> .
> 
0
Utf
6/4/2010 5:56:22 AM
I can't think of anyway to import that other than to use VBA.

-- 
Doug Steele, Microsoft Access MVP
http://www.AccessMVP.com/DJSteele
Co-author: Access 2010 Solutions, published by Wiley
(no e-mails, please!)

"s" <s@mailinator.com> wrote in message 
news:d07736b2-e9a5-4a11-abb8-784778c95eaf@r5g2000yqr.googlegroups.com...
>A text file has data in a format like the below:
>
> Title1
> Cell 1 data
> Title 2
> Cell 2 data
> Title 3
> Cell 3 data
> .
> .
> .
> Title 10 Cell 10 data
>
> Title 11
> Cell 11 data
> .
> .
> .
> Title 15 Cell 15 data
> .
> .
> .
> Title N
> Cell N data
>
> I guess I can read this using some VBA code posted at
> http://www.mvps.org/access/modules/mdl0057.htm and
> http://www.granite.ab.ca/access/readtextfile.htm, add an Instr
> function to it so that when I read the text file, I know when to
> insert the data properly in the table.
>
> 1. Is there another way I can directly import the data into an Access
> 2007 table without VBA code? I know Access allows import from data,
> but my data is not as formatted as I want so the data might end up
> being in the table in a way such as
>
> Field 1 of Access Table
>
> Title 1
> Cell 1
> Title 2
> Cell 2
> .
> .
> .
> Title N
> Cell N
>
> whereas I want it to be like
>
> Field 1                Field 2              Field
> 3...................Field N of the Access table
> Cell 1 data          Cell 2 data        Cell 3 data
>
>
> 2. If I have to use VBA, are the methods I linked to proper(Am i going
> in the right direction) and advisable or are there better/easier ways
> to do what I want to achieve.
>
> Any advice would be welcome. 


0
Douglas
6/4/2010 10:12:13 AM
On Jun 4, 2:24=A0am, s <s...@mailinator.com> wrote:
> A text file has data in a format like the below:
>
> Title1
> Cell 1 data
> Title 2
> Cell 2 data
> Title 3
> Cell 3 data
> .
> .
> .
> Title 10 Cell 10 data
>
> Title 11
> Cell 11 data
> .
> .
> .
> Title 15 Cell 15 data
> .
> .
> .
> Title N
> Cell N data
>
> I guess I can read this using some VBA code posted athttp://www.mvps.org/=
access/modules/mdl0057.htmandhttp://www.granite.ab.ca/access/readtextfile.h=
tm, add an Instr
> function to it so that when I read the text file, I know when to
> insert the data properly in the table.
>
> 1. Is there another way I can directly import the data into an Access
> 2007 table without VBA code? I know Access allows import from data,
> but my data is not as formatted as I want so the data might end up
> being in the table in a way such as
>
> Field 1 of Access Table
>
> Title 1
> Cell 1
> Title 2
> Cell 2
> .
> .
> .
> Title N
> Cell N
>
> whereas I want it to be like
>
> Field 1 =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0Field 2 =A0 =A0 =A0 =A0 =A0 =A0 =
=A0Field
> 3...................Field N of the Access table
> Cell 1 data =A0 =A0 =A0 =A0 =A0Cell 2 data =A0 =A0 =A0 =A0Cell 3 data
>
> 2. If I have to use VBA, are the methods I linked to proper(Am i going
> in the right direction) and advisable or are there better/easier ways
> to do what I want to achieve.
>
> Any advice would be welcome.

The first problem is in rows like > Title 10 Cell 10 data

Is there any way of determining where Title N finishes and Cell N
Data  starts?

If there is you're in business.  If there is no consistent end to
Title N or start to Cell N then I can think of nothing (other than by
hand) that can be done ...

JB
0
jbguernsey
6/5/2010 6:09:47 AM
s wrote:

>A text file has data in a format like the below:
>
>Title1
>Cell 1 data
>Title 2
>Cell 2 data
>Title 3
>Cell 3 data
>.
>Title N
>Cell N data
>
>I guess I can read this using some VBA code posted at
>http://www.mvps.org/access/modules/mdl0057.htm and
>http://www.granite.ab.ca/access/readtextfile.htm, add an Instr
>function to it so that when I read the text file, I know when to
>insert the data properly in the table.
>
>1. Is there another way I can directly import the data into an Access
>2007 table without VBA code? I know Access allows import from data,
>but my data is not as formatted as I want so the data might end up
>being in the table in a way such as
>
>Field 1 of Access Table
>
>Title 1
>Cell 1
>Title 2
>Cell 2
>.
>Title N
>Cell N
>
>whereas I want it to be like
>Field 1                Field 2              Field
>3...................Field N of the Access table
>Cell 1 data          Cell 2 data        Cell 3 data
>
>2. If I have to use VBA, are the methods I linked to proper(Am i going
>in the right direction) and advisable or are there better/easier ways
>to do what I want to achieve.


No, you want the result to be a normalized table:
ID	Title	Data
1		aaa		xxx
2		bbb		yyy
 . . .
n		kkk		zzz

Then you will be able to do interesting things with the
table.  A table that looks like you said you want will be
near unusable anywhere other than printing a spreadsheet.
If a spreadsheet is all you want then use a spreadsheet
program instead of a database.

Note that a table can have up to 255 fields so you N must be
less than that.  Also, a table has no inherent order to the
records it contains so you will need a field such as the ID
field above to preserve the order of the information in the
text file.  (A query is the ONLY way to present sorted
records.)

And, yes you will need to use a fairly simple VBA procedure
to load the data into whatever kind of table you decide to
use.  Chuck Grimsby's class is a good solution to importing
a complex text file, but may be overkill for your fairly
simple text file.  The other one is closer to what I think
you can use, but it does not deal with the every other line
distinction.

OTOH, I think you can just import the text file into a
spreadsheet and transpose it to get what you said you want.

--
Marsh
0
Marshall
6/5/2010 3:03:41 PM
Reply:

Similar Artilces:

Populating Excel from CSV
Hello all, I need to know how to populate an Excel template with data from a external CSV file. Let's say I have this nice pretty colourful templat that is far more pleasing to the eye than the usual chunk of importe CSV data, what are some methods to populate it with data from a CS file?? Any ideas or suggestions or redirections would be much appreciated. Thanks in advance : -- AJMorgan59 ----------------------------------------------------------------------- AJMorgan591's Profile: http://www.excelforum.com/member.php?action=getinfo&userid=2777 View this thread: http://www.ex...

stop extra spaces in text? Word xp. formated font/para ok
I'm using a C.V. template, word xp 2003, when I press the space bar, the whole text moves, and I cannot correct this. I've tried font and paragraph formatting. It's now happening in another template, sometimes I add a word, and again the text spaces increase ruining the document. I would be very grateful for any help. I need to get my C.V. in the post asap, but I've opened word, and am trying to copy/paste into it as a last resort. HELP! Kind Regards, What do you mean by "the whole text moves"? What "text spaces" increase? On Apr 27, 11:20...

Sequence Not populated for the MO Numbers during Data Collection
When data collection is done for the MO Numbers by using the Data Collection Window under Manufaturing, the Sequnce look up does not show the whole list, it only shows NOTES, for the user to select and enter the necessary data. why does this happen in the case of some MO Numbers and in the others it shows all the sequnces as NOTES,MAKE READY, PRESS RUN, WASH UP, MAINTENANCE, REPAIRS, REWIND/INSPECTION, etc. Any information would be much appreciated -- Thanks AshaM It should show all of the router steps. Are some of your steps possibly marked to backflush labor? These steps would not...

Exchange 2003 Standard edition mobile SMS (text messaging)
My mobile phone accepts SMS (text messaging). Is there any way that I make exchange to notify every time that I receive a message or I even can create an email that will send a direct message to my mobile. I understand that I can activate the mobile feature of exchange but it will require the internet to be active on my mobile costing me more money. We have Exchange 2003 Standard edition Thank you. SMS was used by Exchange ActiveSync to initiate ActiveSync in Exchange Server 2003 before SP2. The actual delivery of mail would still require a data connection on your mobile device - t...

Format Text in spreadsheet based on text?
I have a really bad spreadsheet that I am unable to sort or create a list out of without doing a bunch of manual editing so I was thinking that if I could highlight certain text with a different font, color, bold or whatever that would be good enough so I can visually make out the data I need and ignore the rest. Any way of doing that which is simple, quick and a few clicks away? James, You can use conditional formatting to change cell fill colors, font attributes and borders based on the contents of the cell (or other cells, such as in the same row). You have three conditios you can s...

Limited User Access
I am trying to give a user with limited access to the software access to be able to add General Leger accounts in the system. I can't seem to find which option she needs to have to be able to do this. Currently the only things she can do in GP are enter Accounts Payable/Print Checks, add vendors and look at the Trial Balance. Thanks for any help anyone can provide. Melanie: In Security (or Advanced Security) you need to give her access to the Account Card. Frank Hamelly MCP-GP, MCT East Coast Dynamics www.eastcoast-dynamics.com ...

how do populate empty cells with the contents of populated cells .
I have this worksheet problem. The work sheet is thousands of rows long. One of my columns has cells not populated. I would like to get any empty cell in this column to populate itself with the contents of the next populated cell below it? Kind of a "find and replace" action where by an empty cell says to it self;"I'm going fill myself in with the next thing I find below me" I would greatly appreciate if someone can relate how to do this. Thanks Jim, Select the column, use Edit | Go To... Special , Blanks, OK. Then type an equal sign, press the down arrow...

pivot table version issue
A client created a pivot table in Excel 2003. In 2003, the filter dropdown arrows are visible and working. Anyone that opens the file in Excel 2000 does not see the dropdown filters. When you click on the pivot table, Excel does recognize it as a pivot table since the ptable toolbar is active and I am able to access the layout, etc. I copied the data sheet to a new file (using Excel 2003 - different PC), recreated the pivot table and now users with Excel 2000 can see the dropdowns. It is either a setting on the client's PC or a setting within the pivot table itself - but I can't...

Excel opening with Visual Basiic File not Found Error?? Help!!!
When I open up Excel I am receiving an error message "Microsoft Visual Basic File not Found" when I hit help it tells me it is file not found error 53. Can anyone provide me some information on how I need to fix this problem. Help!!!! Thanks, Judd Hi Judd, See responses to similar questions from Tom Ogilvy http://tinyurl.com/9nubf and Dave Peterson: http://tinyurl.com/829pc --- Regards, Norman "jbsand1001" <jbsand1001@discussions.microsoft.com> wrote in message news:A6EAD2E6-E90E-4929-9368-0C96BCADC374@microsoft.com... > When I open up Exce...

Autoplay for Pictures Not Populated
I inserted my XD card in to my card reader today and the autoplay pop up window asking what I wanted Windows to do was blank. For Video, Music and Mixed content the options are there but not for Pictures - it is just blank. I have tried running autofix.exe but this has not fixed it. Any ideas? Is this a Microsoft problem or has something else caused this? It worked fine until today. Thanks. Peachypumpkin wrote: > I inserted my XD card in to my card reader today and the autoplay > pop up window asking what I wanted Windows to do was blank. For > Video, Music and Mi...

Text in an object . . .
I have an eliptical background image that I would like to overlay with an elipse that has the text within the contraints of the elipse. Is that possible in Publisher? Thanks, RLM =.RLM.= wrote: > I have an eliptical background image that I would like to overlay with > an elipse that has the text within the contraints of the elipse. > > Is that possible in Publisher? > > Thanks, > > RLM Put the image on the Master Page - use help to learn how to do this. That really doesn't tell me what I need to know. How can I place text inside of an object (ellipse) and...

Font on Export from Access
Using Excel97, XP machine When I export a query from Access into an Excel spreadsheet, the data comes into Excel always as MS Sans Serif. My default font in both Access and Excel is Comic Sans MS. I would like the data to export into the default I've set. Can someone help with this? Thanks ...

how do i arrange survey data best for a pivot table?
There are pivot table instructions and links on Jon Peltier's web site: http://www.peltiertech.com/Excel/Pivots/pivotstart.htm Mormonchick wrote: -- Debra Dalgleish Excel FAQ, Tips & Book List http://www.contextures.com/tiptech.html ...

outlook.pst file repair tool?
how to repair outlook.pst file which may be corrupted Monis Beraha <anonymous@discussions.microsoft.com> wrote: > how to repair outlook.pst file which may be corrupted Search your HD for scanpst.exe -- Brian Tillman ...

expand quick access toolbar 2007
Hi, Is there a way to expand the quick access toolbar in Excel 2007? I customized the toolbar but it contains more icons to be displayed in a single line, and getting tired on keeping using the drop-down arrow at the end. Thanks! ...

text to voice in EXCEL 2007
How do I activate Text to voice in EXCEL 2007. I have no trouble in Office 2003...but no luck so far in 2007 Yhanks Try this....Click on the Office Button in the upper left hand corner. Click on Excel Options. Click Customize. In the "Choose Commands From:" drop down box you can choose "All Commands". Scroll down to "Speak Cells". Add the speak cell commands to the Quick Access Toolbar. FYI: If you go into the windows control panel and go into Sounds, Speech and Audio Devices, then click on Speech, you can select one of three different computer voi...

Error for multiple users merging data from Access into Word 2003
Hello. I have multiple users who access a Microsoft Access database. The issue I have is that when a user is in the database, a different user is unable to merge data from that database, as Word states that the database file is already in use (which is it). When no one is in the database, all users can merge without issue. When I try to merge data from the database already in use, the error message is as follows: "Test connection failed becuase of an error in initializing provider. Could not use "; file already in use". Is there any simple way that I can make Word '...

City and State Populated by Zip Code
Does anyone know of a way to load a program that when you type in the Zip Code the City and State are automatically populated? I have seen a MSCRM add-in from a company called QAS (http://www.qas.com/). This is more than just Zip Code/City&State. It's a full address validator along with streamlined data entry. They have integrated with CRM via an ISV pop-up and it seems to work fairly smoothly. Matt Parks MVP - Microsoft CRM ---------------------------------------- ---------------------------------------- On 15 Apr 2005 11:13:56 -0700, "Morgan" <tzeoli@att.net> wr...

Need access to credit card transactions that do not settle.
The inability to view transaction details, re-submit as-is or modify unsettled credit card transactions leaves the user with few choices. The credit card vendor may be able to look up the transaction and re-create it from the authorization, but this takes a lot of time and relies on the availability of talented customer service reps at the credit card processor. The alternative is to re-enter the whole transaction in the POS system. This option is usually not available as the user probably does not have the credit card number and, in those cases where they can, the result is a repor...

Populating a field
I want to populate a description field based on a par number that is entered in the field before it. In other owrds, you enter a part number and presto, in the next field, the description is entered for you. Can you help?? Thanx Tom On Thu, 6 Dec 2007 08:48:01 -0800, Tom <Tom@discussions.microsoft.com> wrote: >I want to populate a description field based on a par number that is entered >in the field before it. In other owrds, you enter a part number and presto, >in the next field, the description is entered for you. > >Can you help?? > >Thanx >Tom If yo...

Pivot Table: Misordered date Grouping
I'm having a problem where the grouping is coming out wrong. I have dates ranging from October 2003 to June 2004. When I group by month, it gives me a January - November groupings vs. a October - June groupings. In other words, the October and November months should be in the first two columns given their dates come first. Thanks, Mark G. Hi, Can you post a piece of your data/spreadsheet? I can't get it to do what you describe. jeff >-----Original Message----- >I'm having a problem where the grouping is coming out >wrong. I have dates ranging from October 2...

accessing outlook data using excel macro
Hey all, I'm very new with all of this, however, my problem is this: I have a bunch of data in excel which I then want to compare to certain "task" fields in outlook (ie. the "Due Date" column in Tasks for example), then update those outlook fields with new data. I was able to do it in an Outlook macro, but not in the Excel macro. I get a "User defined type not defined" error when trying to define a namespace variable as "Outlook.Namespace" Any help would be greatly appreciated. Thanks. ...

web access #4
when using web access as the main way to check email, is there any way that when clicking an email hyperlink in a web page that i can get it to open a new message window using web access? as it works at the moment, when logged into web access and i click an email link it opens up outlook express and tries to get me to fill in all the server details etc... ...

Populate Textbox
Hi, I have a table with part numbers and locations. There are multiple locations per part number. I would like to populate a listbox with a filtered list of the locations for the part number shown on my form. I'm not very good at vba and have been looking all over for code, but can't find anything suitable. Thanks You have multiple locations per part number. So first, let's get your form set up correctly. When you have a one-to-many format (one salesman, many products or one part number, many locations) you need a form and subform. The Mainform would be for...

Text Box within a Text Box
I am creating a brochure and want to put a testimonial (text box) in within a services text box. I would like the text in the services text box to continue to flow around the testimonial text box. How do I make this work? THANKS, MEG Right-click the services text box, format text box, layout tab, select square or tight. Be certain the testimonial text box is in front. Actually it should flow around automatically. -- Mary Sauer MS MVP http://office.microsoft.com/ http://msauer.mvps.org/ news://msnews.microsoft.com "MEG" <MEG@discussions.microsoft.com> wrote in messag...