If this is your first visit, be sure to check out the FAQ by clicking the link above. You may have to register before you can post: click the register link above to proceed. To start viewing messages, select the forum that you want to visit from the selection below. |
|
|
|
Thread Tools | Display Modes |
#11
|
|||
|
|||
Import Table Structure from Excel
I had assumed that your 'data map' was doing more than that. From this description, I'm now wondering why you don't just use the built-in text import wizard, as described by David elsewhere in this thread. -- Brendan Reynolds Access MVP "Les H" wrote in message ... Thanks to Jeff and Brendan. I can tell you are trying to help but I think the truth is that I'm not communicating well enough what I want to do. As a consequence, you are not responding to the question I'm trying to ask. By the by, the 255 columns thing is exactly the same in Dbase. I circumvent this by breaking the data into two or more tables. Here's WHY I'm asking the question - Borland no longer supports Dbase as a product and, with operating system changes etc., it's only a matter of time before I'll be unable to run Dbase on a PC (unless I keep a legacy system just for the purpose). That means I MUST think about migrating to a different piece of database software. (My first thought was just to use Excel but its data import facilities are primitve, requiring maximum manual manipulation and mouse clicking). It also means that, yes, I can continue to use Dbase in the short term but not in the longer term. Here's WHAT I need to achieve. I need to get the data from surveys - over which I have no editorial or technical control - into tables. The data arrives in text files with a data map, as I've said. Here's what I DO when I have survey data to load. The data map (spreadsheet) lists the variable names, column number in the record, and column width. I rapidly convert this (with a few simple Excel formulae) to a data table structure(s), treating every field as text. Each structure is saved from Excel as a Dbase table. I then use Dbase's CREATE USING command to create a new table from the structure table. Finally, I populate the new table with Dbase's APPEND FROM command. Here is my criterion for a satisfactory solution. I need to get from raw data to populated data tables in the shortest time and with the least possible effort, given that every survey is unique in its structure. As I've said, this normally takes me less than half an hour with Dbase. I have to do very little typing and most of the mouse operations involve holding the left button down and using the scroll wheel. My suspicion is that Access's data import facilties are just as primitive as those of Excel and that it's facilities for table definition/construction are equally primitive, albeit that they are wrapped up in fancy clothes. That, at least, is my perception based on my attempts so far to use Access to do this seemingly simple task. I don't doubt that I COULD use VBA or, if I had 2003 stuff, .NET programming to do the job - I can certainly write the programs - but two things mitigate against this. First, every situation I have to deal with is uniquely structured and would have to be rewritten each time. Second, everything I've ever done with VBA and .NET has been like pulling teeth once you stray from the fine line laid down in the help files. The simple truth is that I would rather not go this route unless I have to. I don't know whether this is any clearer. Or is it that I just need to read your answers so far as "No, there is no easy way to do this simple task"? Regards, "Brendan Reynolds" wrote: As Jeff says, the maximum number of fields in a JET table is 255, so if any of your tables contain more fields than that you will not be able to import them into a single JET table. If none of your tables exceed 255 fields, then you could continue to use dBase to create the tables from your Excel files, and your Access application could import or link the resulting dBase tables. If you can find a way to create an XML Schema from your Excel specifications, you might be able to use that XML Schema to create the tables. I can't say for sure, as I have not made much use of the XML features in recent versions of Access, but I think that should be possible. Other than that, you would need to write code to read the specification from the Excel files and create the JET tables. Provided the Excel files stick to a standardized format and layout, it would not be particularly difficult to do so. -- Brendan Reynolds Access MVP "Les H" wrote in message ... Jeff, Thanks for your response but it doesn't seem to address my question. I am not really interested in "well normalized" - all I want to do is get data, that arrives in a text file (with an Excel spreadsheet that tells me what columns are what), into an Access table quickly and efficiently. No amount of worrying over the fine details of database/table design will matter until that first simple task is achieved. With good old Dbase, this was a matter of 10-20 minutes work (and very little typing/clicking) for a few hundred columns of data. My question is whether there is a similarly simple method of getting my data into an Access table? Regards, "Jeff Boyce" wrote: Creating a well-normalized relational database table (e.g., in Access) "with a few hundred fields" is something of a contradiction in terms. A scan of this newsgroup (tablesdbdesign) will show a strong consensus that a well-normalized table will only rarely have more than 30 fields. The fact that the survey was originally conducted using Excel is a clue ... Take a look at work that Duane H. has done on building well-normalized survey databases. Perhaps there's a way to "parse" your raw data into something that Access can more fully utilize. See: Duane Hookom has a sample survey database at http://rogersaccesslibrary.com/Other...p#Hookom,Duane -- Regards Jeff Boyce Microsoft Office/Access MVP http://mvp.support.microsoft.com/ Microsoft IT Academy Program Mentor http://microsoftitacademy.com/ Microsoft Registered Partner https://partner.microsoft.com/ "Les H" wrote in message ... I have been a long time user of Borland Visual Dbase (because of its convenience and simplicity of use) and would like to migrate to Access but have found it unwieldy. I frequently receive data from surveys with a few hundred fields. The data arrives as a .dat (text) file plus a data map (Excel). It has always been an easy task to convert the data map into a table specification, which I can export as a Dbase file. Dbase has a simple CREATE USING command, which creates a new table from an existing "structure" table. My question: Is there a simple(!) way that this can be achieved in Access? I have some experience of writing macros with VBA (Excel and Powerpoint) but would prefer to avoid the hassle. Thanks. |
#12
|
|||
|
|||
Import Table Structure from Excel
"Are you sure .." I was, and I was wrong. We will still have to get by with
a measly 255 columns in Access 2007. How will we cope? :- :- "Brendan Reynolds" wrote in message ... "David Cox" wrote in message ... snip I am using Access 2007 Beta (free). 255 columns no longer applies. snip Are you sure about that, David? I had not heard of any change in that area, but I don't have anything with more than 255 columns with which to test. -- Brendan Reynolds Access MVP |
#13
|
|||
|
|||
Import Table Structure from Excel
Phew! You had me worried there for a minute! :-) There's always SQL Server. 1,024 columns per base table, 4,096 columns per SELECT statement. Scary! -- Brendan Reynolds Access MVP "David Cox" wrote in message ... "Are you sure .." I was, and I was wrong. We will still have to get by with a measly 255 columns in Access 2007. How will we cope? :- :- "Brendan Reynolds" wrote in message ... "David Cox" wrote in message ... snip I am using Access 2007 Beta (free). 255 columns no longer applies. snip Are you sure about that, David? I had not heard of any change in that area, but I don't have anything with more than 255 columns with which to test. -- Brendan Reynolds Access MVP |
#14
|
|||
|
|||
Import Table Structure from Excel
Unless I am much mistaken, the text import wizard is exactly what I was
referring to as Excel's "primitive" import system. If you know where the fields are, how big they are, etc. then I'd agree. My situation is that the only way I know where to find anything is via the data map. By converting the data map into a table structure, I save huge amounts of time. I can assure you all that, in general, it is NOT possible to achieve the text import in 6 minutes! To tell the truth, I am disappointed (but not surprised) to find that the "technology" has lost the ability to do some of the basic tasks simply. It is a sad but almost universal trend in commercial technology development - the imperative to justify upgrading. The thought of having to spend EXTRA money to do SIMPLER tasks is, frankly, a red rag to a bull as far as I'm concerned. Thanks for your efforts. I think I probably have the answer I feared most. Regards, "Brendan Reynolds" wrote: I had assumed that your 'data map' was doing more than that. From this description, I'm now wondering why you don't just use the built-in text import wizard, as described by David elsewhere in this thread. -- Brendan Reynolds Access MVP "Les H" wrote in message ... Thanks to Jeff and Brendan. I can tell you are trying to help but I think the truth is that I'm not communicating well enough what I want to do. As a consequence, you are not responding to the question I'm trying to ask. By the by, the 255 columns thing is exactly the same in Dbase. I circumvent this by breaking the data into two or more tables. Here's WHY I'm asking the question - Borland no longer supports Dbase as a product and, with operating system changes etc., it's only a matter of time before I'll be unable to run Dbase on a PC (unless I keep a legacy system just for the purpose). That means I MUST think about migrating to a different piece of database software. (My first thought was just to use Excel but its data import facilities are primitve, requiring maximum manual manipulation and mouse clicking). It also means that, yes, I can continue to use Dbase in the short term but not in the longer term. Here's WHAT I need to achieve. I need to get the data from surveys - over which I have no editorial or technical control - into tables. The data arrives in text files with a data map, as I've said. Here's what I DO when I have survey data to load. The data map (spreadsheet) lists the variable names, column number in the record, and column width. I rapidly convert this (with a few simple Excel formulae) to a data table structure(s), treating every field as text. Each structure is saved from Excel as a Dbase table. I then use Dbase's CREATE USING command to create a new table from the structure table. Finally, I populate the new table with Dbase's APPEND FROM command. Here is my criterion for a satisfactory solution. I need to get from raw data to populated data tables in the shortest time and with the least possible effort, given that every survey is unique in its structure. As I've said, this normally takes me less than half an hour with Dbase. I have to do very little typing and most of the mouse operations involve holding the left button down and using the scroll wheel. My suspicion is that Access's data import facilties are just as primitive as those of Excel and that it's facilities for table definition/construction are equally primitive, albeit that they are wrapped up in fancy clothes. That, at least, is my perception based on my attempts so far to use Access to do this seemingly simple task. I don't doubt that I COULD use VBA or, if I had 2003 stuff, .NET programming to do the job - I can certainly write the programs - but two things mitigate against this. First, every situation I have to deal with is uniquely structured and would have to be rewritten each time. Second, everything I've ever done with VBA and .NET has been like pulling teeth once you stray from the fine line laid down in the help files. The simple truth is that I would rather not go this route unless I have to. I don't know whether this is any clearer. Or is it that I just need to read your answers so far as "No, there is no easy way to do this simple task"? Regards, "Brendan Reynolds" wrote: As Jeff says, the maximum number of fields in a JET table is 255, so if any of your tables contain more fields than that you will not be able to import them into a single JET table. If none of your tables exceed 255 fields, then you could continue to use dBase to create the tables from your Excel files, and your Access application could import or link the resulting dBase tables. If you can find a way to create an XML Schema from your Excel specifications, you might be able to use that XML Schema to create the tables. I can't say for sure, as I have not made much use of the XML features in recent versions of Access, but I think that should be possible. Other than that, you would need to write code to read the specification from the Excel files and create the JET tables. Provided the Excel files stick to a standardized format and layout, it would not be particularly difficult to do so. -- Brendan Reynolds Access MVP "Les H" wrote in message ... Jeff, Thanks for your response but it doesn't seem to address my question. I am not really interested in "well normalized" - all I want to do is get data, that arrives in a text file (with an Excel spreadsheet that tells me what columns are what), into an Access table quickly and efficiently. No amount of worrying over the fine details of database/table design will matter until that first simple task is achieved. With good old Dbase, this was a matter of 10-20 minutes work (and very little typing/clicking) for a few hundred columns of data. My question is whether there is a similarly simple method of getting my data into an Access table? Regards, "Jeff Boyce" wrote: Creating a well-normalized relational database table (e.g., in Access) "with a few hundred fields" is something of a contradiction in terms. A scan of this newsgroup (tablesdbdesign) will show a strong consensus that a well-normalized table will only rarely have more than 30 fields. The fact that the survey was originally conducted using Excel is a clue ... Take a look at work that Duane H. has done on building well-normalized survey databases. Perhaps there's a way to "parse" your raw data into something that Access can more fully utilize. See: Duane Hookom has a sample survey database at http://rogersaccesslibrary.com/Other...p#Hookom,Duane -- Regards Jeff Boyce Microsoft Office/Access MVP http://mvp.support.microsoft.com/ Microsoft IT Academy Program Mentor http://microsoftitacademy.com/ Microsoft Registered Partner https://partner.microsoft.com/ "Les H" wrote in message ... I have been a long time user of Borland Visual Dbase (because of its convenience and simplicity of use) and would like to migrate to Access but have found it unwieldy. I frequently receive data from surveys with a few hundred fields. The data arrives as a .dat (text) file plus a data map (Excel). It has always been an easy task to convert the data map into a table specification, which I can export as a Dbase file. Dbase has a simple CREATE USING command, which creates a new table from an existing "structure" table. My question: Is there a simple(!) way that this can be achieved in Access? I have some experience of writing macros with VBA (Excel and Powerpoint) but would prefer to avoid the hassle. Thanks. |
#15
|
|||
|
|||
Import Table Structure from Excel
Hi Les,
You may not be out of luck just yet. I'm not sure what your data map looks like in Excel, but you might be able to use some VBA code to programmatically create a schema.ini file. Here is a KB article that demonstrates doing this for tables in Access: How to programmatically create a Schema.ini file in Access 2000 http://support.microsoft.com/kb/210001 I tested this code and it seems to work fine with local and linked tables. It even worked with an ODBC linked table in a SQL Server database. Try the sample out in Northwind, and examine the contents of the resulting schema.ini text file. The point I'm trying to make is that you might be able to easily convert your data map in Excel into a schema.ini file. The schema.ini file is used to help with importing text files, using the TransferText method. Here is a KB article that discusses how to do this: ACC2000: How to Use Schema.ini to Access Text Data http://support.microsoft.com/kb/210073 Tom Wickerath Microsoft Access MVP http://www.access.qbuilt.com/html/ex...tributors.html http://www.access.qbuilt.com/html/search.html __________________________________________ "Les H" wrote: Unless I am much mistaken, the text import wizard is exactly what I was referring to as Excel's "primitive" import system. If you know where the fields are, how big they are, etc. then I'd agree. My situation is that the only way I know where to find anything is via the data map. By converting the data map into a table structure, I save huge amounts of time. I can assure you all that, in general, it is NOT possible to achieve the text import in 6 minutes! To tell the truth, I am disappointed (but not surprised) to find that the "technology" has lost the ability to do some of the basic tasks simply. It is a sad but almost universal trend in commercial technology development - the imperative to justify upgrading. The thought of having to spend EXTRA money to do SIMPLER tasks is, frankly, a red rag to a bull as far as I'm concerned. Thanks for your efforts. I think I probably have the answer I feared most. Regards, |
#16
|
|||
|
|||
Import Table Structure from Excel
Tom,
Thank you for the leads to the articles. Schema.ini is indeed what I've been looking for. I could be picky and say that it's a heck of a lot more complicated than it needs to be (lots of "unnecessary" overhead content in the Schema.ini file) but that would be churlish. With a bit of programmatic leger demain (and four or five extra steps in my process chain) I may now be able to migrate to Access if and when my Dbase becomes unusuable. Regards, "Tom Wickerath" wrote: Hi Les, You may not be out of luck just yet. I'm not sure what your data map looks like in Excel, but you might be able to use some VBA code to programmatically create a schema.ini file. Here is a KB article that demonstrates doing this for tables in Access: How to programmatically create a Schema.ini file in Access 2000 http://support.microsoft.com/kb/210001 I tested this code and it seems to work fine with local and linked tables. It even worked with an ODBC linked table in a SQL Server database. Try the sample out in Northwind, and examine the contents of the resulting schema.ini text file. The point I'm trying to make is that you might be able to easily convert your data map in Excel into a schema.ini file. The schema.ini file is used to help with importing text files, using the TransferText method. Here is a KB article that discusses how to do this: ACC2000: How to Use Schema.ini to Access Text Data http://support.microsoft.com/kb/210073 Tom Wickerath Microsoft Access MVP http://www.access.qbuilt.com/html/ex...tributors.html http://www.access.qbuilt.com/html/search.html __________________________________________ |
#17
|
|||
|
|||
Import Table Structure from Excel
Hi Les,
Although I haven't seen the structure of a typical data map in your Excel file, I'm guessing that with some minor VBA programming that you might be able to reduce the time your current process takes from approx. 6 minutes to just a few seconds. The development of the code required would take some time, but once it was tested, debugged, and working properly, I think you would be very happy you made the switch. If you care to send me a few Excel data map files, along with their corresponding text files, I can take a look to see how easy or difficult this might be. My e-mail address is available at the bottom of the contributors page shown in my signature. Please do not post your e-mail address, or mine, to a newsgroup message. Tom Wickerath Microsoft Access MVP http://www.access.qbuilt.com/html/ex...tributors.html http://www.access.qbuilt.com/html/search.html __________________________________________ "Les H" wrote: Tom, Thank you for the leads to the articles. Schema.ini is indeed what I've been looking for. I could be picky and say that it's a heck of a lot more complicated than it needs to be (lots of "unnecessary" overhead content in the Schema.ini file) but that would be churlish. With a bit of programmatic leger demain (and four or five extra steps in my process chain) I may now be able to migrate to Access if and when my Dbase becomes unusuable. Regards, |
|
Thread Tools | |
Display Modes | |
|
|