f



Create Macro to import fixed width text from clipboard or text file and update table

Hi,

I have a MS access database table for which I regularly need to import
fixed width text data. At present I have to to cut and paste the text
data from its source to a text file, save the file, import the text
file as fixed width text [it is not naturally delimited], and then run
an update query to copy the appropriate info into fields of a different
table.

Is it possible to write a macro to do all these steps? Also, is it
possible to import directly from the clipboard (to skip the step of
making a text file)?

Any help provided would be greatly appreciated.

Best wishes,

George Hadley
ghadley_00@yahoo.com

0
ghadley_00 (35)
12/30/2005 12:32:49 PM
comp.databases.ms-access 42670 articles. 0 followers. Post Follow

1 Replies
993 Views

Similar Articles

[PageSpeed] 59

ghadley_00@yahoo.com wrote:

> Hi,
> 
> I have a MS access database table for which I regularly need to import
> fixed width text data. At present I have to to cut and paste the text
> data from its source to a text file, save the file, import the text
> file as fixed width text [it is not naturally delimited], and then run
> an update query to copy the appropriate info into fields of a different
> table.
> 
> Is it possible to write a macro to do all these steps? Also, is it
> possible to import directly from the clipboard (to skip the step of
> making a text file)?
> 
> Any help provided would be greatly appreciated.
> 
> Best wishes,
> 
> George Hadley
> ghadley_00@yahoo.com
> 

First, I would create an import spec.

Next, will the text file always have the same name?  If not, how will 
you know which text file to import?  (If you need a FileOpen dialog, go 
to http://www.mvps.org/access and check out APIs.  The first API should 
have the code you need to get FileOpen to work.)

You can create a macro for importing but why not create some code for it 
instead.  The method you should look at is TransferText in help.  This 
is part of Docmd.  Using code is simple enough for this task, but a 
macro can be used.
0
oil (4047)
12/30/2005 5:58:51 PM
Reply:

Similar Artilces:

Importing text files into MS Access using a Macro
I have 2 problems: 1. I want to import a single text file into an access table using a Macro. I am however getting an error that I need to put a specification name argument. What does this mean? It also suggests that I add a schema.ini in the source folder. What does this .ini do and how do I create and use it? 2. After this is resolved, I would like to be able to import several text files using a macro into the same table. Thanks I'm sure others will be able to shed more light on this: To create an import/export specification, launch the Import Wizard: File | Get External Data | Import... Point to the desired txt file Select the "Advanced..." button Specify the field delimiter, the text qualifier (you might want to select "" in case some of your incoming text fields have punctuations, etc.), and the field names and data types, etc. Then select "Save As" and give that set of specifications a name. You can now use this set of import/export specifications in a macro or in a VBA module. To see these specs as they are saved in the mdb file, select Tools | Options | View, and check System Objects. You'll now see the tables, MSysIMEXSpecs and MSysIMEXColumns. PS you might want to "unshow" these system objects once you've looked at them so you don't accidentally hose them... chimambo@googlemail.com wrote: > I have 2 problems: > > 1. I want to import a single text file into an access table using a &...

How to import a tab delimited text file into MS Access database
Hi, I have the following task to be done in VC++ 6.0. I have a tab delimited text file that needs to be imported into a MS Access table. The table is created and the table structure is defined using DAO methods. So now I have to fill this table with the data in the text files. For a few rows in the text file, the number of columns could be less than the number of columns in the table. Can anyone please post me the code to do this. Thanks a ton in advance. Pradeep wrote: > I have the following task to be done in VC++ 6.0. We'd be happy to give you the VBA code, but do you really want to throw away your C++ and use Microsoft Access for the job? If you want to keep C++, you can ask for C++ code in another newsgroup, because we'd just be guessing. -- Message posted via http://www.accessmonster.com ...

Wrapping Text
I'm trying to export an Access table to a pipe-delimited text file (with no text qualifier) and it keeps wrapping the text for one of the tables. None of the fields are particularly long, so I'm not sure why it's doing this. Any ideas how I can stop it from wrapping the text in the text file? -- Message posted via AccessMonster.com http://www.accessmonster.com/Uwe/Forums.aspx/databases-ms-access/200803/1 Leviathan wrote: >I'm trying to export an Access table to a pipe-delimited text file (with no >text qualifier) and it keeps wrapping the text for one of the tables. None of >the fields are particularly long, so I'm not sure why it's doing this. Any >ideas how I can stop it from wrapping the text in the text file? Nevermind, figured it out. Looks like there was a carriage return in defining one of the cells in the query. -- Message posted via AccessMonster.com http://www.accessmonster.com/Uwe/Forums.aspx/databases-ms-access/200803/1 ...

read from Text file using VB and Insert into SQL table until eof text file reach
Dear All, my text file like following, a1, b1, c1, d1, e1 a2, b2, c2, d2, e2 a3, b3, c3, d3, e3 a4, b4, c4, d4, e4 ................. and im going to insert this values into SQL Tables with columns, [a, b, c, d, e] how im going to do so? i use below src code to do but i can not get the next line and next line of text file until it finished insert Private Sub mot_Click() 'Shell ("C:\Program Files\Microsoft Office\Office11\MSACCESS.EXE c:\fisherdb. mdb /x ImportOpsNilai") 'frmPicCd.Show Dim db_connect ' Path = App.Path & "\ERP consumable code.xls" Dim objFSO Dim objFile, objTextStream Dim strContent Const ForReading = 1 Dim FileName, IEPath, strC1 As String FileName = "U:\bureport\buops\RepNilai.txt" Set objFSO = CreateObject("Scripting.FileSystemObject") Set objFile = objFSO.GetFile(FileName) Set objTextStream = objFile.OpenAsTextStream(ForReading, TristateUseDefault) If objFSO.FileExists(FileName) Then Set objFile = objFSO.OpenTextFile(FileName, ForReading) 'While Not objFile.EOF strC1 = objFile.ReadLine Dim word ReDim word(2) word = Split(strC1, ",") ' Dim t1, t2, t3 As String ' MsgBox RTrim(LTrim(word(44))) ' t1 = Replace(word(0), """", "...

Old ms-access file, showing wrong text in access xp
Dear sirs: I've one old .mdb file ( since 97). now when I open it, it converts most of text to strange unicode characters. can I stop this conversion and ask access to just show original non-unicode characters? Thanks Bijan ...

No text file import choice in Access 97 import dialog box
I have been through every trick I know, or has been suggested. I have a one to two million line fixed field database in text format. I want to bring it into Access 97. When I use the external data import, the list of files to import does not include text files of any type, only other database formats. On a 102,000 line text file, I was able to split it with Word 97, import the split files into Excel 97 one at a time, and then save them as dbf files. Access can read those. The bigger file would require days of grief to do that with it. I have been able to do this on other systems, but my copy of Office 97 Pro refuses to install all the file translation stuff for Access. It goes through the motions, and reports success, but nothing has changed. Two of the choices in the add/remove in setup will do neither. Can't remove them, and can't install them. I had a lot of trouble with Office 97 when I first installed it in a different directory from a partial install of Office 2000. Later I uninstalled 2000, but it seemed to have left a whole lot of stuff behind, and reinstalling or repairing 97 didn't improve things. So, I finally uninstalled 97, and then used the wonderful delete key on every file or folder that looked like it might have anything to do with Office of any kind. Granted, a bit of a judgment call, and something else was sure to break, but I didn't want to have to reinstall all four Gigs of stuff - no backup then, just original disks - Then I used R...

Importing a text file to Access
I want to add a button to a form to import a text delimited file. The File is delimited by "^" and so I have created a specification file by manually impoting the text file, using the advanced button and saving the specification. Naively I thought I just needed to add the specification file name to variable strSpec for the following command to work DoCmd.TransferText acImportDelim, strSpec, strOutFile, strInFile, -1 When i run this from the button there is an error message returned which states the specification file does not exist but when doing this manually it is still in the database. Can anyone tell me how to get the command to find the specification file or how to code it directly into the above command? Thanks Hi, Phil. > When i run this from the button there is an error message returned > which states the specification file does not exist but when doing this > manually it is still in the database. Most likely, the specification name is misspelled. Use Debug.Print to print the strSpec variable to the Immediate window to see exactly what you're passing to the TransferText method. If the specification name is spelled correctly, then ensure that: 1.) strOutFile contains the table name you're importing into, and 2.) strInFile is the full path and file name you're importing the data from, and 3.) the specification is saved in the current database file, not a library database file or other open database file. HTH. Gun...

Import text file to table
I will shortly be receiving data in the form of a text file, like this: id: 123456 first name: Fred surname: Bloggs age: 26 and so on, for about 60 fields. Each line ends with a carriage return/line feed. My question is - how do I import the data into a table (A2003) where the field names match those in the text file? I think I could probably grind out a solution but it would be horribly long winded - there must be a neat solution to this. Any help gratefully received Thanks Dave "Dave G @ K2" <DaveGriffiths70@gmail.com> wrote in news:1152868325.293739.168460@s13g2000cwa.googlegroups.com: > I will shortly be receiving data in the form of a text file, > like this: > > id: 123456 > first name: Fred > surname: Bloggs > age: 26 > > and so on, for about 60 fields. Each line ends with a carriage > return/line feed. > > My question is - how do I import the data into a table (A2003) > where the field names match those in the text file? I think I > could probably grind out a solution but it would be horribly > long winded - there must be a neat solution to this. > > Any help gratefully received > Thanks > Dave > If the name: value pair is always separated with a colon, import into a two-field temporary table, (an autonumber and a textfield). then code a loop that does something like this.. do until RsFrom.EOF iBreak = instr(1,rsFrom!textField,":") stfieldName = left(rsFrom!...

Import Text file into Access
Hello. I am saving an Excel file as a text file then importing it into Access using a specification. One of the fields in the file is a currency formatted with commas. Those numbers over 999 are being interpreted as text and not imported. I could modify the Excel file not to use the commas before I import, but I was looking for something more automated. I tried making it a text field in the specification, but it still only imported those numbers under 1,000. Any help would be appreciated. Thanks. I found the problem. Thanks. On Jan 23, 11:59=A0am, angelasg <asguill...@gmail.com> wrote: > Hello. > > I am saving an Excel file as a text file then importing it into Access > using a specification. =A0One of the fields in the file is a currency > formatted with commas. =A0Those numbers over 999 are being interpreted > as text and not imported. =A0I could modify the Excel file not to use > the commas before I import, but I was looking for something more > automated. > > I tried making it a text field in the specification, but it still only > imported those numbers under 1,000. > > Any help would be appreciated. > > Thanks. Might help some other participant here, in the future, if you'd say what the problem was and how you fixed it. Newsgroups are for sharing information, not just for getting free help. Larry "angelasg" <asguillory@gmail.com> wrote I found the problem. Thanks. angelasg <...

Import MS Office Excel Comma Separated Values Files into a MS Access 2003 database
Hi, I have been tasked with Importing MS Office Excel Comma Separated Values Files into a MS Access 2003 database. These files were exported from a DB2 database.I have also been given the DDL code. I have Googled a solution to this task and have tried creating these tables using the DDL code in a query within a blank MS Access database. Is there a more expedient way to achieve this? I noticed, for example, that a field in DB2 has a data type of "decimal" while the same field would have the data type of "number". Thanks, Zuf What follows is the DDL: -------...

Create a table regarding a text file
Hello, I have this .txt file : Roger|tow25$rank259 Isabelle|tow36$rank24 Pascal|tow12$rank29 Serge�|tow45$rank5 Michel|tow1245$rank45478 Fr�d�ric|tow1$rank125425 And this programm php3 <?php $fichier = "classeur.txt"; if($fp = fopen($fichier,"r")){ $ligne=1; echo "<table border=1 bordercolor=\"#00CCFF\" width=500>\n"; echo "<tr align=center><td colspan=3>TITRE</td>"; while (!feof($fp)) { list( $name, $tampon ) = explode( "|tow", $fp ); list( $tow, $obj ) = explode ( "$rank", $tampon ); echo &quo...

text-text
Wondering how what I input to my UTF-8 terminal gets passed along through my patched [1] trn ... Cyrillic: А Б В Г Д Е Ж З И Й К Л М Н О П а б в г д е ж з и й к л м н о п IPA: ᴀ ᴁ ᴂ ᴃ ᴄ ᴅ ᴆ ᴇ ᴈ ᴉ ᴊ ᴋ ᴌ ᴍ ᴎ ᴏ ɀ Ɂ ɂ Ƀ Ʉ Ʌ Ɇ ɇ Ɉ ɉ Ɋ ɋ Ɍ ɍ Ɏ ɏ [1] https://groups.google.com/d/msg/comp.sys.raspberry-pi/7Z37Hdrm0DM/6aqD-reXFzAJ ...

Import text file to access from VB6
I am trying to import records from a csv textfile to an access 2000 database from VB6. My SQL INSERT statement below works fine if the date and time fields are removed. With the date and time fields I get a Syntax Error when trying to import. Any suggestions? When the records are in the text file they look like so: root,08/27/03,11:03:08,Receiving,482789,START Function importprodtxt() Dim proddb1 As Database Dim f As Integer, strvalues, strSQL, counter, strdte, strcdate, strctime Dim struser, strdate As String, strtime As String, strtask, strdocno As Long, strstatus strdte = F...

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 2...

text + text
What is "text + text" supposed to do right now? It doesn't seem very useful to me. What about making "text + text" as an equivalent for "text || text"? Most strongly-typed programming languages do this. And MS SQL Server too, I think (CMIIW). -- dave ---------------------------(end of broadcast)--------------------------- TIP 1: subscribe and unsubscribe commands go to majordomo@postgresql.org Am Freitag, 8. Oktober 2004 12:57 schrieb David Garamond: > What is "text + text" supposed to do right now? Nothing. > What about making "text + text" as an equivalent for "text > || text"? Most strongly-typed programming languages do this. And MS SQL > Server too, I think (CMIIW). What would this gain except for bloat? It's not like SQL is utterly compatible with any programming language; users will still have to learn all the operators anyway. -- Peter Eisentraut http://developer.postgresql.org/~petere/ ---------------------------(end of broadcast)--------------------------- TIP 9: the planner will ignore your desire to choose an index scan if your joining column's datatypes do not match Peter Eisentraut wrote: >>What is "text + text" supposed to do right now? > > Nothing. Then are these bugs? (7.4.5 and 8.0.0beta1 give same results). Frankly, the current behaviour is quite strange to me. ------------------ =...

Updating column in a text file without overwriting original text
I have a text file with both numbers and words and I want to select a specific numeric column and replace it with new numeric values. I want to replace the 6th column of: $$ $$ FORCE Data $$ FORCE 2 71153 01.0 0.0 0.00117 0.0 FORCE 2 71209 01.0 0.0 0.00179 0.0 FORCE 2 71268 01.0 0.0 0.00239 0.0 FORCE 2 71325 01.0 0.0 0.00296 0.0 FORCE 2 71676 01.0 0.0 0.00345 0.0 FORCE 2 71384 01.0 0.0 0.00383 0.0 FORCE 2 71620 01.0 0.0 0.00407 0.0 FORCE 2 71444 01.0 0.0 0.00406 0.0 FORCE 2 71560 01.0 0.0 0.00382 0.0 FORCE 2 71504 01.0 0.0 0.00317 0.0 ENDDATA $$ I have it reading in the file and selecting this specific row, I just cant get it to update it with New_force=[1;2;3;4;5;6]; without overwriting the rest of the data in the file. This is what I have so far: fid = fopen('NRELS814_10 elements_simple_solver_gui3.fem','r+') data=textscan(fid,'%s','delimiter', '','whitespace',''); for i=1:10 row= strread(data{1}{i+86610},'%s','delimiter',' ') force_i=row(6); force(i)=force_i end fclose(fid); New_force=[1;2;3;4;5;6]; dlmwrite('NRELS814_10 elements_simple_solver_gui3.fem&...

Create a Link in Access to a Text File
I am writing a little routine to perform the following operations from an Acces 97 mdb: 1. create a fixed width text file 2. create/establish a table type link to the text file in Access 3. allow the user to view the text contained in the text file via an Access form. I have been able to achieve this, albeit with one major limitation. That is that the name of the text file needs to be known prior to runtime. In other words currently I cant let the user make up their own name for the file. I have to dictate this so that I can pick up a link to the file, based on a link specification that I...

PUTting a fixed width text file
Dear colleagues, I've been working with a number of procedures to read data from fixed width text files in one format and writing back subsets of it into multiple fixed width text files in a different format. A recurring issue has been when a variable being read in has leading blanks, it appears as left justified in the output file. However, those leading blanks are significant. Sometimes the variables also have trailing blanks. So what I end up doing is reading in a dummy variable that starts at the same point as the real variable that I want to write and ends at a point in the file ...

Create MS/ACCESS database app for non-ms/access users
Is it possible to create an MS/ACCESS database application and package it for users who do not have MS/ACCESS loaded in their PCs? I was told that this is possible, but I don't know what software is needed. Do you know? If so, please answer this post. Thanks. SueB *** Sent via Developersdex http://www.developersdex.com *** Don't just participate in USENET...get rewarded for it! Per Sue Bricker: >Is it possible to create an MS/ACCESS database application and package >it for users who do not have MS/ACCESS loaded in their PCs? > >I was told that this is possible, but...

Unbound text box, text>255, MS Access/PostgreSQL
Hello. I have the following problem with MS Access/PostgreSQL combination: There is a form in Access that has an unbound text box, used for entering a commentary of a batch of records. There is a DAO Append Query that has a parameter that is passed from the text box using parameter of DAO QueryDef object. I adjusted B7 parameter (Text as LongVarchar) in connection string (ODBC driver) to 1, so that Access links PostgreSQL Text fields as Memo fields in Access. The intention was to be able to enter unlimited length commentary into the field. But, although I can enter unlimited text by using...

Fixed-length text file to database script
Hi Guys, I'm new to Python (mostly) and I'm wanting to use it for a new project I'm faced with. I have a machine (PLC) that is dumping its test results into a fixed- length text file. I need to pull this data into a database (MySQL most likely) so that I can access it with Crystal Reports to create daily reports for my engineers. I've been reading the Python manual for about a week now and I'm learning a lot. Unfortunately, I was given a deadline today that I cannot meet without a little help. I need to know how to write a script that will DAILY pull this text file in...

Framework for import plain text files into databases
Hi everybody. I'm looking for a framework that allow me to map text files into tables. I need to load files with different structures into one or many tables under some customer-specific rules. I was searching for a while but it looks like there isn't anything like that, so if any of you knows a framework or plugin that allow me to do that, please let me know. Thanks in advance. <german.largo@gmail.com> wrote: > I'm looking for a framework that allow me to map text files into > tables. > What do you mean by a framework? There's no obvious reason to desire...

access 97 : importing text files with vb
i had some problems trying to alter this code in order to import different text files into the access 97 program. it works properly however the users will be selecting different types of text files. for instance, the prefix will be stored in the cboAcct (combo box), ie BC, DB, AF, EG. so if i tried to contenate the string, it would be cboAcct "_DDA_CR" . but instead, the code imports and creates a new table called "_DDA_CR" instead of a new table called "BC_DDA_CR." does anyone know what i am missing in the code? thanks in advance,- jung. Dim InDialog As Offic...

Importing text file, create matrix and plotting
Hi Group, i have a text file and the information is written this way: Data_1 1 .. m Data_n 1 .. m I want to import this into n matrices of size m x m so i can plot them by using any surf command, and of course, ignoring the title Data_etc. Can anyone help me with this ? Regards, Victor "victor.herasme@gmail.com" <victor.herasme@gmail.com> wrote in message <04897c79-1197-4a84-b3a9- b2db1182dcac@p25g2000hsf.googlegroups.com>... > Hi Group, > > i have a text file and the information is written this way: > > Data_1 > 1 > . > m > > Data...

Web resources about - Create Macro to import fixed width text from clipboard or text file and update table - comp.databases.ms-access

Clipboard - Wikipedia, the free encyclopedia
A clipboard is a thin, rigid board with a clip at the top for holding paper in place. A clipboard is typically used to support paper with one ...

Water polo coach's clipboard for iPhone, iPod touch, and iPad on the iTunes App Store
Get Water polo coach's clipboard on the App Store. See screenshots and ratings, and read customer reviews.

Clipboard and Pen 2 - Flickr - Photo Sharing!
Clipboard and Pen 2 - Past and present tools of the collection agency trade.

Clip Stack: A Clipboard Your Phone Needs – XDA App Review - YouTube
and millions of cat videos. Smartphones have brought friends and family closer. Find something funny, adorable, or twisted that your friends ...

New from Google: Paid web apps, drawings in web clipboard, latest breathtaking 45° imagery in Maps
Stunning 45-degree views are now available in Maps for more places in the US and abroad. Never content with resting on its laurels, Google have ...

Google Docs, Sheets, & Slides iOS apps now let you ‘Make a copy’, copy links to clipboard, more
... function for documents, spreadsheets, and presentations across all three apps, you’ll also now be able to copy links to your device’s clipboard. ...

Salesforce Acquires Bookmarking Startup Clipboard for More than $10M
Salesforce has acquired Clipboard , a social bookmarking service that had built interesting hooks for saving rich web content across devices, ...

OneClip Is Microsoft's Upcoming Cross-Platform Cloud Clipboard Service
There's little doubt in anyone's mind that Microsoft has been marching forward with its cross-platform strategy over the past months. The company ...

First Major Command-C Update Brings Clipboard History And More
Version 1.1 also gives users the ability to share clipboards between Macs.

Take full control of the Windows clipboard with CLCL
If you’ve been using PCs for longer than 5 minutes then you’ve almost certainly run into the limitations of the Windows clipboard, and tried ...

Resources last updated: 3/21/2016 6:07:50 AM