Manipulate import Format (1 Viewer)

[msp]

Hi,

I am importing some data from a excel spread sheet to my access database. However the format in the spreadsheet is causing me some issue. This is shown below.

The format the spread sheet is in

First column System Name (which is the name of the system) i.e Server1,This only ever has one value and one row per system. Next column is “Related_Hardware”. This is a comma separated list of the server name I.e (Server1, Server2 Server 3).

I need to manipulate this data so that instead of having on row per system and a comma separated list for all the servers in that system. I have a new row for each system with a single server.

Example

Current Format

{System1} (The system column), { Server1, Server2 Server 3} (The Related_Hardware column)

Required Format
[system column] [Related_Hardware]
{System1}, {Server1}
{System1}, {erver2}
{System1},{ {Server3}

Any help or guidance as to how I can get the data from the current format to the . required format would be greatly appreciated.

It is quite a simple problem with my explaination with a little complicated.

 

[The_Doc_Man]

Import your spreadsheet into a table that won't live very long. Using your example, it could easily be a four-field table.

Now write a three-part UNION query that has two final columns, [server] and [related]. The columns come from your original column 1 and one of columns 2, 3, or 4. (One separate SELECT for each column.) If there is a possibility that some columns will be empty, make the SELECT clauses have where clauses to exclude such columns from the result-set.

Now write a MAKE-TABLE query that creates a table based on the UNION query. OR create the table manually and write an APPEND query that adds the records of the UNION query.

Once this is done you can trash the originally imported table. No VBA required.

OR, you could just directly use the UNION query if all you needed was the list you described. Of course, if you are going to have other columns in your final table, the APPEND method is probably the best option.

 

[msp]

Import your spreadsheet into a table that won't live very long. Using your example, it could easily be a four-field table.

Now write a three-part UNION query that has two final columns, [server] and [related]. The columns come from your original column 1 and one of columns 2, 3, or 4. (One separate SELECT for each column.) If there is a possibility that some columns will be empty, make the SELECT clauses have where clauses to exclude such columns from the result-set.

Now write a MAKE-TABLE query that creates a table based on the UNION query. OR create the table manually and write an APPEND query that adds the records of the UNION query.




Once this is done you can trash the originally imported table. No VBA required.

OR, you could just directly use the UNION query if all you needed was the list you described. Of course, if you are going to have other columns in your final table, the APPEND method is probably the best option.

Docman, Thanks for the quick answer. This solution appears to looklike it could work, although some of the system have as much as a 30 related hardware attached wich will mean at least 30 queries in the union query.

 

[msp]

After looking at the data some system have as much as 200 servers attached is there any way this could be done in VB. (although my VB skills need improving)