473,287 Members | 1,834 Online
Bytes | Software Development & Data Engineering Community
Post Job

Home Posts Topics Members FAQ

Join Bytes to post your question to a community of 473,287 software developers and data experts.

Import External File and change 1 field before Insert

Hi,

I have been manually importing a text file on a weekly basis to an
Access table. The data on one field is about to change (happens to be
the first field). It was numeric, and is defined as numeric on the
Access table. Now it will have a prefix of "K", for example: K00075.

I would like to create a procedure in my Access database that strips
the "K" off the field, and inserts the rest of the data into the table.
I know I can do it manually in Word or an application like that before
I import it, but I'd prefer to streamline the process now.

How can I can read the input file, strip the K off, and write an
output file without the K. Then import this 2nd file into my database?
I have never written anything within Access to read/write external
files before (only in COBOL, only a tad bit different!). Is this done
in a module, a macro, or what? Is that the best way to do this?

I would just like to sort-of replicate my manual process, which was:
Get External Data, Import file with delimiters, Load into Existing
table AFTER STRIPPING THE K. Can I automate something like that?

(I cannot get the file created without the K.)

Thanks a bunch in advance,
Lorirobn
ps - I have posted this also to microsoft.public.access, don't know if
the groups are related

Dec 5 '06 #1
1 1964

probably the easiest way is to import to a temp table then use a query
to strip off the K and write it to the table you want it in - something
like right([FieldName,len(FieldName)-1) .

If you are writing in VB or working with macros, you can automate the
entire process down to a button click. Take a look at import
specifications to help with this too.
HTH

lorirobn wrote:
Hi,

I have been manually importing a text file on a weekly basis to an
Access table. The data on one field is about to change (happens to be
the first field). It was numeric, and is defined as numeric on the
Access table. Now it will have a prefix of "K", for example: K00075.

I would like to create a procedure in my Access database that strips
the "K" off the field, and inserts the rest of the data into the table.
I know I can do it manually in Word or an application like that before
I import it, but I'd prefer to streamline the process now.

How can I can read the input file, strip the K off, and write an
output file without the K. Then import this 2nd file into my database?
I have never written anything within Access to read/write external
files before (only in COBOL, only a tad bit different!). Is this done
in a module, a macro, or what? Is that the best way to do this?

I would just like to sort-of replicate my manual process, which was:
Get External Data, Import file with delimiters, Load into Existing
table AFTER STRIPPING THE K. Can I automate something like that?

(I cannot get the file created without the K.)

Thanks a bunch in advance,
Lorirobn
ps - I have posted this also to microsoft.public.access, don't know if
the groups are related
Dec 5 '06 #2

This thread has been closed and replies have been disabled. Please start a new discussion.

Similar topics

14
by: Demetris | last post by:
Hello people! I have a table with more than 30 million rows,a lot of columns and indexes. We need to change a column which is dec(15,2) and nullable to have a default value of zero. As I know you...
3
by: Ong | last post by:
can anyone know how to import dbf file into access by selecting some of the field
3
by: deko | last post by:
I've been trying to use the Access Import Wizard to expedite importing data into my mdb. The nice thing about the wizard is that I can import from different file formats - txt, xls, even Outlook -...
3
by: ninrulz | last post by:
I will try to explain my situation. I know that it is hard to offers solutions without fully understanding what people would like to achieve. I receive 2 csv files every month. The csv files...
1
by: Child of His | last post by:
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...
9
by: cabrenner | last post by:
I am new to SQL Server, and migrating part of an Access application to SSE. I am trying to insert a comma delimited file into SSE 2005. I am able to run a BULK INSERT statement on a simple file,...
49
by: Martin Unsal | last post by:
I'm using Python for what is becoming a sizeable project and I'm already running into problems organizing code and importing packages. I feel like the Python package system, in particular the...
2
by: GarryJones | last post by:
(I am sorry if this is the wrong group for this posting, I cant find a group on usenet for phpmyadmin, but maybe someone would be nice enough to answer me anyway....) I need to import data into...
10
by: nisp | last post by:
Hi all ! I'm trying to capture stderr of an external module I use in my python program. I'm doing this by setting up a class in my module overwriting the stderr file object method write. The...
2
isladogs
by: isladogs | last post by:
The next Access Europe meeting will be on Wednesday 7 Feb 2024 starting at 18:00 UK time (6PM UTC) and finishing at about 19:30 (7.30PM). In this month's session, the creator of the excellent VBE...
0
by: DolphinDB | last post by:
The formulas of 101 quantitative trading alphas used by WorldQuant were presented in the paper 101 Formulaic Alphas. However, some formulas are complex, leading to challenges in calculation. Take...
0
by: DolphinDB | last post by:
Tired of spending countless mintues downsampling your data? Look no further! In this article, you’ll learn how to efficiently downsample 6.48 billion high-frequency records to 61 million...
0
by: ryjfgjl | last post by:
ExcelToDatabase: batch import excel into database automatically...
0
isladogs
by: isladogs | last post by:
The next Access Europe meeting will be on Wednesday 6 Mar 2024 starting at 18:00 UK time (6PM UTC) and finishing at about 19:15 (7.15PM). In this month's session, we are pleased to welcome back...
0
by: Vimpel783 | last post by:
Hello! Guys, I found this code on the Internet, but I need to modify it a little. It works well, the problem is this: Data is sent from only one cell, in this case B5, but it is necessary that data...
0
by: jfyes | last post by:
As a hardware engineer, after seeing that CEIWEI recently released a new tool for Modbus RTU Over TCP/UDP filtering and monitoring, I actively went to its official website to take a look. It turned...
0
by: ArrayDB | last post by:
The error message I've encountered is; ERROR:root:Error generating model response: exception: access violation writing 0x0000000000005140, which seems to be indicative of an access violation...
1
by: PapaRatzi | last post by:
Hello, I am teaching myself MS Access forms design and Visual Basic. I've created a table to capture a list of Top 30 singles and forms to capture new entries. The final step is a form (unbound)...

By using Bytes.com and it's services, you agree to our Privacy Policy and Terms of Use.

To disable or enable advertisements and analytics tracking please visit the manage ads & tracking page.