Google Groups no longer supports new Usenet posts or subscriptions. Historical content remains viewable.
Dismiss
Convert first record only
85 views
Skip to first unread message
guu...@gmail.com
Oct 27, 2012, 1:45:59 PM10/27/12
to
The table contains various data types (dates, integers, etc)
How do I convert the first record only to 'camel caps' (first letter of the word to caps) and convert the first record to field names?
Thanks.
ggl
How do I convert the first record only to 'camel caps' (first letter of the word to caps) and convert the first record to field names?
Thanks.
ggl
guu...@gmail.com
Oct 27, 2012, 1:57:13 PM10/27/12
to
On Saturday, October 27, 2012 10:45:59 AM UTC-7, guu...@gmail.com wrote:
> The table contains various data types (dates, integers, etc) How do I convert the first record only to 'camel caps' (first letter of the word to caps) and convert the first record to field names? Thanks. ggl
By the way, in case you are wondering why I just don't do it manually --we will be importing csv files frequently from an Oracle app every day into Access.
> The table contains various data types (dates, integers, etc) How do I convert the first record only to 'camel caps' (first letter of the word to caps) and convert the first record to field names? Thanks. ggl
Bob Barrows
Oct 27, 2012, 2:18:28 PM10/27/12
to
guu...@gmail.com
Oct 27, 2012, 3:02:04 PM10/27/12
to
On Saturday, October 27, 2012 11:19:18 AM UTC-7, Bob Barrows wrote:
wrote: > On Saturday, October 27, 2012 10:45:59 AM UTC-7, guu...@gmail.com > wrote: >> The table contains various data types (dates, integers, etc) How do >> I convert the first record only to 'camel caps' (first letter of the >> word to caps) and convert the first record to field names? Thanks. >> ggl > > By the way, in case you are wondering why I just don't do it manually > --we will be importing csv files frequently from an Oracle app every > day into Access. Use the import wizard ...
This is after the data has already been imported. The data being imported was in turn exported from Oracle. Apparently the csv files before the import have a hidden character which creates the imported file with the first record always a blank in the Access table. So the second record after the import are actually the field names. I simply delete the first (blank record) in Access.
wrote: > On Saturday, October 27, 2012 10:45:59 AM UTC-7, guu...@gmail.com > wrote: >> The table contains various data types (dates, integers, etc) How do >> I convert the first record only to 'camel caps' (first letter of the >> word to caps) and convert the first record to field names? Thanks. >> ggl > > By the way, in case you are wondering why I just don't do it manually > --we will be importing csv files frequently from an Oracle app every > day into Access. Use the import wizard ...
The problem now is how to turn the former 2nd record and now the first record to fieldnames.
By the way, I don't want to mess around with the raw csv files. These files are about a terabyte in size and millions of records. I rather not use another program eg. grep, sed, text editor, etc to pre-process.
Bob Barrows
Oct 27, 2012, 3:52:59 PM10/27/12
to
guu...@gmail.com wrote:
> On Saturday, October 27, 2012 11:19:18 AM UTC-7, Bob Barrows wrote:
> wrote: > On Saturday, October 27, 2012 10:45:59 AM UTC-7,
> guu...@gmail.com > wrote: >> The table contains various data types
> (dates, integers, etc) How do >> I convert the first record only to
> 'camel caps' (first letter of the >> word to caps) and convert the
> first record to field names? Thanks. >> ggl > > By the way, in case
> you are wondering why I just don't do it manually > --we will be
> importing csv files frequently from an Oracle app every > day into
> Access. Use the import wizard ...
>
> This is after the data has already been imported.
Then you need to delete the imported data and start again. Run the import
> On Saturday, October 27, 2012 11:19:18 AM UTC-7, Bob Barrows wrote:
> wrote: > On Saturday, October 27, 2012 10:45:59 AM UTC-7,
> guu...@gmail.com > wrote: >> The table contains various data types
> (dates, integers, etc) How do >> I convert the first record only to
> 'camel caps' (first letter of the >> word to caps) and convert the
> first record to field names? Thanks. >> ggl > > By the way, in case
> you are wondering why I just don't do it manually > --we will be
> importing csv files frequently from an Oracle app every > day into
> Access. Use the import wizard ...
>
> This is after the data has already been imported.
wizard and tell it the first row contains field names. It won't be able to
affect their case, but really ... why do you care about the case of the
field names? If it's that important, create a view (a saved query) that
provides the column aliases as appropriate.
> The data being
> imported was in turn exported from Oracle. Apparently the csv files
> before the import have a hidden character which creates the imported
> file with the first record always a blank in the Access table. So
> the second record after the import are actually the field names. I
> simply delete the first (blank record) in Access.
>
> The problem now is how to turn the former 2nd record and now the
> first record to fieldnames.
>
> By the way, I don't want to mess around with the raw csv files. These
> files are about a terabyte in size and millions of records. I rather
> not use another program eg. grep, sed, text editor, etc to
> pre-process.
need to write VBA code to accomplish what you want. If you need it done
quickly, I suggest hiring a consultant.
Wait, it just occurred to me that you already have data in a table in Access
... that table can be used as the source for a saved query that excludes the
first row and supplies column aliases for the field names.
The Frog
Oct 27, 2012, 7:36:24 PM10/27/12
to
Wait a minute. A terabyte of data into Access from a csv? No way.
To do the cleanup and importing the OP is talking about they should
be using a tool built for the job. This sort of thing needs data
validation and process control, and most likely error handling and
logging. This is ETL pure and simple. Do the heavy lifting where you
can process it properly.
If you want to know more just say so here and I'll point you in the
right direction. Punching through a terabyte of csv with vba and
Access limited to 2 gigs is making very hard work of things.
--
Cheers
The Frog
To do the cleanup and importing the OP is talking about they should
be using a tool built for the job. This sort of thing needs data
validation and process control, and most likely error handling and
logging. This is ETL pure and simple. Do the heavy lifting where you
can process it properly.
If you want to know more just say so here and I'll point you in the
right direction. Punching through a terabyte of csv with vba and
Access limited to 2 gigs is making very hard work of things.
--
Cheers
The Frog
guu...@gmail.com
Oct 27, 2012, 10:15:19 PM10/27/12
to
On Saturday, October 27, 2012 4:37:14 PM UTC-7, The Frog wrote:
> Wait a minute. A terabyte of data into Access from a csv? No way. To do the cleanup and importing the OP is talking about they should be using a tool built for the job. This sort of thing needs data validation and process control, and most likely error handling and logging. This is ETL pure and simple. Do the heavy lifting where you can process it properly. If you want to know more just say so here and I'll point you in the right direction. Punching through a terabyte of csv with vba and Access limited to 2 gigs is making very hard work of things. -- Cheers The Frog
I know about the 2 gig limit. One csv file I am looking at is about 300MB uncompressed. It takes about 10-15 minutes to import it to Access 2010 and this results in an .accdb file with a file size of about 800 MB and about 2.6 MB records. I'm using a workstation running an Intel quad core at 4+ Ghz and 16 GB of memory on an SSD disk. Am I saying this right -- this is approximately 1 TB of data or should I be saying it's only a 300 MB file?
> Wait a minute. A terabyte of data into Access from a csv? No way. To do the cleanup and importing the OP is talking about they should be using a tool built for the job. This sort of thing needs data validation and process control, and most likely error handling and logging. This is ETL pure and simple. Do the heavy lifting where you can process it properly. If you want to know more just say so here and I'll point you in the right direction. Punching through a terabyte of csv with vba and Access limited to 2 gigs is making very hard work of things. -- Cheers The Frog
Where do I draw the line before I can say Access is not up to job and what ETL tools would you then suggest? 500K records? 1M records? 500MB accdb file size?
David Hare-Scott
Oct 28, 2012, 1:14:21 AM10/28/12
to
guu...@gmail.com wrote:
>
> Where do I draw the line before I can say Access is not up to job and
> what ETL tools would you then suggest? 500K records? 1M records?
> 500MB accdb file size?
What exactly are you going to do with this data? Why use Access instead of
>
> Where do I draw the line before I can say Access is not up to job and
> what ETL tools would you then suggest? 500K records? 1M records?
> 500MB accdb file size?
oracle? Why are you importing it instead of linking to the oracle
database?
Access is limited to 2gig in a native database but you can link to other
database formats and you can use many back-ends in native format each
holding 2 gig.
Until we know what you are up to there is no way to say where you will reach
a limit in Access.
D
guu...@gmail.com
Nov 1, 2012, 1:27:17 PM11/1/12
to
On Saturday, October 27, 2012 10:14:24 PM UTC-7, David Hare-Scott wrote:
> > Where do I draw the line before I can say Access is not up to job and > what ETL tools would you then suggest? 500K records? 1M records? > 500MB accdb file size? What exactly are you going to do with this data? Why use Access instead of oracle? Why are you importing it instead of linking to the oracle database? Access is limited to 2gig in a native database but you can link to other database formats and you can use many back-ends in native format each holding 2 gig. Until we know what you are up to there is no way to say where you will reach a limit in Access.
Reply:
> > Where do I draw the line before I can say Access is not up to job and > what ETL tools would you then suggest? 500K records? 1M records? > 500MB accdb file size? What exactly are you going to do with this data? Why use Access instead of oracle? Why are you importing it instead of linking to the oracle database? Access is limited to 2gig in a native database but you can link to other database formats and you can use many back-ends in native format each holding 2 gig. Until we know what you are up to there is no way to say where you will reach a limit in Access.
Extracted and transferred data originates from nightly batch production processes. The data must be loaded into an easy to use readily available no extra cost app (in this case Access 2010) by a clerical worker (aka not too techie analyst)for generating daily summary pretty-print exception reports before 6 am for mid-level management who will then assign it to hunter-killer groups :-). The analyst does not have access or permission to interact with unix based character based sql legacy app which uses Oracle as the back-end database. The 'mission-critical' legacy app and Oracle back-end is hosted in a genormous data-center in another state. Getting them to make changes requires change management involvement, cost justification, approvals, etc. Getting access permissions requires several signatures too. Unix and sql skills required also. Not worth the effort or multi-divisional exposure. Hence Access.
David Hare-Scott
Nov 1, 2012, 8:42:30 PM11/1/12
to
can generate these programatically based on the date, name of the import
file, etc. The import process seems to be under control so it comes down to
the performance of your reports. I would experiment with indexing the
fields that are used for joining and restriction as there will be a trade
off between the cost of running queries on unindexed tables and doing the
indexing. Obviously if there are many reports run on the same data or each
may be run several times then indexing once at the start is likely to be
cheaper. So far I haven't seen anything that Access can't do, whether it
can do it quick enough for your liking is another matter.
If it is any consolation I have had an Access table with over 2 million
records and searches on indexed fields took a fraction of a second.
David
guu...@gmail.com
Nov 2, 2012, 1:05:12 AM11/2/12
to
On Thursday, November 1, 2012 5:42:10 PM UTC-7, David Hare-Scott wrote:
> gu...@gmail.com wrote: > On Saturday, October 27, 2012 10:14:24 PM UTC-7, David Hare-Scott > wrote: >>> Where do I draw the line before I can say Access is not up to job >>> and > what ETL tools would you then suggest? 500K records? 1M >>> records? > 500MB accdb file size? What exactly are you going to do >>> with this data? Why use Access instead of oracle? Why are you >>> importing it instead of linking to the oracle database? Access is >>> limited to 2gig in a native database but you can link to other >>> database formats and you can use many back-ends in native format >>> each holding 2 gig. Until we know what you are up to there is no >>> way to say where you will reach a limit in Access. > > Reply: > Extracted and transferred data originates from nightly batch > production processes. The data must be loaded into an easy to use > readily available no extra cost app (in this case Access 2010) by a > clerical worker (aka not too techie analyst)for generating daily > summary pretty-print exception reports before 6 am for mid-level > management who will then assign it to hunter-killer groups :-). The > analyst does not have access or permission to interact with unix > based character based sql legacy app which uses Oracle as the > back-end database. The 'mission-critical' legacy app and Oracle > back-end is hosted in a genormous data-center in another state. > Getting them to make changes requires change management involvement, > cost justification, approvals, etc. Getting access permissions > requires several signatures too. Unix and sql skills required also. > Not worth the effort or multi-divisional exposure. Hence Access. I would be inclined to put each extract into a separate Access database, you can generate these programatically based on the date, name of the import file, etc. The import process seems to be under control so it comes down to the performance of your reports. I would experiment with indexing the fields that are used for joining and restriction as there will be a trade off between the cost of running queries on unindexed tables and doing the indexing. Obviously if there are many reports run on the same data or each may be run several times then indexing once at the start is likely to be cheaper. So far I haven't seen anything that Access can't do, whether it can do it quick enough for your liking is another matter. If it is any consolation I have had an Access table with over 2 million records and searches on indexed fields took a fraction of a second. David
Good to know. What was the size of your .accdb file? Thank you very much.
> gu...@gmail.com wrote: > On Saturday, October 27, 2012 10:14:24 PM UTC-7, David Hare-Scott > wrote: >>> Where do I draw the line before I can say Access is not up to job >>> and > what ETL tools would you then suggest? 500K records? 1M >>> records? > 500MB accdb file size? What exactly are you going to do >>> with this data? Why use Access instead of oracle? Why are you >>> importing it instead of linking to the oracle database? Access is >>> limited to 2gig in a native database but you can link to other >>> database formats and you can use many back-ends in native format >>> each holding 2 gig. Until we know what you are up to there is no >>> way to say where you will reach a limit in Access. > > Reply: > Extracted and transferred data originates from nightly batch > production processes. The data must be loaded into an easy to use > readily available no extra cost app (in this case Access 2010) by a > clerical worker (aka not too techie analyst)for generating daily > summary pretty-print exception reports before 6 am for mid-level > management who will then assign it to hunter-killer groups :-). The > analyst does not have access or permission to interact with unix > based character based sql legacy app which uses Oracle as the > back-end database. The 'mission-critical' legacy app and Oracle > back-end is hosted in a genormous data-center in another state. > Getting them to make changes requires change management involvement, > cost justification, approvals, etc. Getting access permissions > requires several signatures too. Unix and sql skills required also. > Not worth the effort or multi-divisional exposure. Hence Access. I would be inclined to put each extract into a separate Access database, you can generate these programatically based on the date, name of the import file, etc. The import process seems to be under control so it comes down to the performance of your reports. I would experiment with indexing the fields that are used for joining and restriction as there will be a trade off between the cost of running queries on unindexed tables and doing the indexing. Obviously if there are many reports run on the same data or each may be run several times then indexing once at the start is likely to be cheaper. So far I haven't seen anything that Access can't do, whether it can do it quick enough for your liking is another matter. If it is any consolation I have had an Access table with over 2 million records and searches on indexed fields took a fraction of a second. David
Good to know. What was the size of your .accdb file? Thank you very much.
guu...@gmail.com
Nov 2, 2012, 1:12:25 AM11/2/12
to
On Thursday, November 1, 2012 10:05:13 PM UTC-7, guu...@gmail.com wrote:
> On Thursday, November 1, 2012 5:42:10 PM UTC-7, David Hare-Scott wrote: > gu...@gmail.com wrote: > On Saturday, October 27, 2012 10:14:24 PM UTC-7, David Hare-Scott > wrote: >>> Where do I draw the line before I can say Access is not up to job >>> and > what ETL tools would you then suggest? 500K records? 1M >>> records? > 500MB accdb file size? What exactly are you going to do >>> with this data? Why use Access instead of oracle? Why are you >>> importing it instead of linking to the oracle database? Access is >>> limited to 2gig in a native database but you can link to other >>> database formats and you can use many back-ends in native format >>> each holding 2 gig. Until we know what you are up to there is no >>> way to say where you will reach a limit in Access. > > Reply: > Extracted and transferred data originates from nightly batch > production processes. The data must be loaded into an easy to use > readily available no extra cost app (in this case Access 2010) by a > clerical worker (aka not too techie analyst)for generating daily > summary pretty-print exception reports before 6 am for mid-level > management who will then assign it to hunter-killer groups :-). The > analyst does not have access or permission to interact with unix > based character based sql legacy app which uses Oracle as the > back-end database. The 'mission-critical' legacy app and Oracle > back-end is hosted in a genormous data-center in another state. > Getting them to make changes requires change management involvement, > cost justification, approvals, etc. Getting access permissions > requires several signatures too. Unix and sql skills required also. > Not worth the effort or multi-divisional exposure. Hence Access. I would be inclined to put each extract into a separate Access database, you can generate these programatically based on the date, name of the import file, etc. The import process seems to be under control so it comes down to the performance of your reports. I would experiment with indexing the fields that are used for joining and restriction as there will be a trade off between the cost of running queries on unindexed tables and doing the indexing. Obviously if there are many reports run on the same data or each may be run several times then indexing once at the start is likely to be cheaper. So far I haven't seen anything that Access can't do, whether it can do it quick enough for your liking is another matter. If it is any consolation I have had an Access table with over 2 million records and searches on indexed fields took a fraction of a second. David Good to know. What was the size of your .accdb file? Thank you very much.
Forgot to ask. Can I convert a csv file to an accdb file from the Windows 7 command line? Can I launch an Access macro from the command line? Thanks again.
> On Thursday, November 1, 2012 5:42:10 PM UTC-7, David Hare-Scott wrote: > gu...@gmail.com wrote: > On Saturday, October 27, 2012 10:14:24 PM UTC-7, David Hare-Scott > wrote: >>> Where do I draw the line before I can say Access is not up to job >>> and > what ETL tools would you then suggest? 500K records? 1M >>> records? > 500MB accdb file size? What exactly are you going to do >>> with this data? Why use Access instead of oracle? Why are you >>> importing it instead of linking to the oracle database? Access is >>> limited to 2gig in a native database but you can link to other >>> database formats and you can use many back-ends in native format >>> each holding 2 gig. Until we know what you are up to there is no >>> way to say where you will reach a limit in Access. > > Reply: > Extracted and transferred data originates from nightly batch > production processes. The data must be loaded into an easy to use > readily available no extra cost app (in this case Access 2010) by a > clerical worker (aka not too techie analyst)for generating daily > summary pretty-print exception reports before 6 am for mid-level > management who will then assign it to hunter-killer groups :-). The > analyst does not have access or permission to interact with unix > based character based sql legacy app which uses Oracle as the > back-end database. The 'mission-critical' legacy app and Oracle > back-end is hosted in a genormous data-center in another state. > Getting them to make changes requires change management involvement, > cost justification, approvals, etc. Getting access permissions > requires several signatures too. Unix and sql skills required also. > Not worth the effort or multi-divisional exposure. Hence Access. I would be inclined to put each extract into a separate Access database, you can generate these programatically based on the date, name of the import file, etc. The import process seems to be under control so it comes down to the performance of your reports. I would experiment with indexing the fields that are used for joining and restriction as there will be a trade off between the cost of running queries on unindexed tables and doing the indexing. Obviously if there are many reports run on the same data or each may be run several times then indexing once at the start is likely to be cheaper. So far I haven't seen anything that Access can't do, whether it can do it quick enough for your liking is another matter. If it is any consolation I have had an Access table with over 2 million records and searches on indexed fields took a fraction of a second. David Good to know. What was the size of your .accdb file? Thank you very much.
David Hare-Scott
Nov 2, 2012, 1:36:17 AM11/2/12
to
>> Thank you very much.
>
> Forgot to ask. Can I convert a csv file to an accdb file from the
> Windows 7 command line?
or command line arguments.
>Can I launch an Access macro from the command
> line? Thanks again.
opens the specified database with specified arguments. If it is only ever
going to do one function you can also have the database start the process
you want whenever it opens and so avoid command line args. And yes you can
defeat that auto startup if you want to work on the databse instead of run
the process (or just have two different icons).
D
0 new messages