Question

Locked

MS Excel- Can this be done

By jimmy-jam ·
Ok gang I've got a puzzler for you.

I was assigned a project and was given an excel spreadsheet with some data on it which I need to convert to a database and web enable it. This part is no problem.

Here's the problem. The one column of the spread sheet contain multiple two character codes separated by a forward slash.

eg. AB/CD/EF

When I import this spreadsheet into the database I only want one code per record

eg. AB
CD
EF
otherwise the reports we run against the database will not be acurate. I know I can do a manual cut and paste row by row and make my edits, the question is, is there some automated process I can use to do this for me?

This will knock days off the project. Help? Thanks.

Jim

This conversation is currently closed to new comments.

14 total posts (Page 1 of 2)   01 | 02   Next
| Thread display: Collapse - | Expand +

All Answers

Collapse -

It depends

by Router boy In reply to MS Excel- Can this be don ...

It depends on exactly how you are going about getting the data into the DB. If you use the data import function in access you might be able to tweak the custom delimeter function under Get external data -> Import -> advanced options. If this is not option you can script the data import process to automatically seperate the data but that might take a little longer to accomplish.

Collapse -

Access is an option

by jimmy-jam In reply to It depends

but I don't see advanced options. I stepped through the whole import process but couldn't find where to tweak my column delimiters. Also, if I was able to make this change would that clone the data in my other fields within that row? That's going to prove important through this process.

Collapse -

Advanced Options

by Router boy In reply to Access is an option

Is located under File -> Get External Data at this point you browse to the file you are looking to import. Once the file is selected you will have a an advanced button. You can walk through the process of massaging the data to get the desired output and save the process similar to a macro. You can use a little VB to call the operation each time.

As for the other option you could use a little code to accomplish that and its fairly simple to write.

Collapse -

Script it

by IC-IT In reply to MS Excel- Can this be don ...

use VB or VBA to open the workbook, copy the contents to a new workbook, add a few columns and then extract the 3 sets (from the one cell) into the original and 2 new columns.
Here are a few (partial :-( ) examples to get you started.
Someone out there could probably knock the script out in minutes (I would need days ;-) ).

http://preview.tinyurl.com/63uvyb
http://www.themssforum.com/Excel/copy-part-138086/

Collapse -

If you want to split the multiple values into separate columns

by ThumbsUp2 In reply to MS Excel- Can this be don ...

You can use the Text to Column feature using the forward slash as the delimiter. Then, each row (record) would have one column for each code. Once the data is separated, import the spreadsheet.

Collapse -

Right solution

I was hoping someone would mention this, it is much easier this way, you just need to know that the tool exist:)

Collapse -

I use it all the time...

by ThumbsUp2 In reply to Right solution

... especially to prepare spreadsheet data to be imported into databases.

Collapse -

Right solution

I was hoping someone would mention this, it is much easier this way, you just need to know that the tool exist:)

Collapse -

Is there a way to convert text to rows?

by jimmy-jam In reply to If you want to split the ...

Text to Column is pretty close to what I want. The big thing I need to be able to do is take the contents of the A column and if it has data delimited by a / to spilt it off into a separate row and bring a copy of the data in column B-S with it.

Collapse -

Not that I know of.... unfortunately

by ThumbsUp2 In reply to Is there a way to convert ...

But, you could certainly use Text to Column to split the data out, then insert a blank row below the one you need, highlight and copy down (ctrl+D). You'll need to move some of the data that has been split out over to the appropriate column for each row added and eventually get it all to where you could delete the extra columns.

Back to Storage Forum
14 total posts (Page 1 of 2)   01 | 02   Next

Related Discussions

Related Forums