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 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
This thread has been closed and replies have been disabled. Please start a new discussion. Similar topics
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...
|
by: Ong |
last post by:
can anyone know how to import dbf file into access by selecting some of the
field
|
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 -...
|
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...
|
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...
|
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,...
|
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...
|
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...
|
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...
|
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...
|
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...
|
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...
|
by: ryjfgjl |
last post by:
ExcelToDatabase: batch import excel into database automatically...
|
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...
|
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...
|
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...
|
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...
|
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)...
| |