Excel to Access

[ocm]

Greetings,

Currently, we use Excel 2010 to input detailed information about a given data element and use save as CSV to export to a third party application that we use for data definition purposes. This has been very time consuming, and we thought MS Access forms should simplify the data entry process. We can then create reports as CSV to export.
I’ve attached two samples:

1. The way data is inputted currently in excel (sample1)
2. The end result in the third party application (sample2)

Can you please give me some guidance the best way to accomplish this in Access?

TIA,

Regards

[rpeare]

any chance you could give an example of what you want the actual output to be, the word document looks more like a data definition

for example take the first row of your sample1 spreadsheet and show us what it's supposed to look like in it's end state.

[rpeare]

sorry, and your spreadsheet doesn't appear to have data either, any chance you could give a sample of what you your BEFORE actually looks like and what you want your AFTER to look like?

doesn't have to be actual data, just something better than this because your spreadsheet is basically two row but multiple pieces of data per row per cell which I'm not quite following

[ocm]

rpeare, thanks for the reply. Sorry for the confusion.

The third party application allows us to import a CSV template limited to 10 fields (A – J). Since we want to document additional information about a given data element (like workflow, example, status etc), we entered multiple pieces of date per cell. The actual excel is made up of close to 100 rows (one row for each data element) the attach two rows is just an example.

So based on my sample1, the Access form would have:

Field Name1
Description:
Workflow:
Keep History:
.
.
.
Field Name 2
Description:
Workflow:
Keep History:
.
.
.
And create a report (CSV) format when imported to the third party application looks like my sample2.
Maybe I’m making it harder than it is.

TIA,

[rpeare]

I understand your description, but your files only contain setup not something that would actually be in a file (which I'd need in order to mess around with it and offer a suggestion)

For instance you have carriage returns within cells that would need to be parsed out and put into separate fields within an access table

so... create a bogus file that has bogus data in it, then post it here.

Please make it clear which pieces of information on the spreadsheet you want to extract and put into separate fields (maybe by bolding them on the excel file or some other such mechanism) so it's clear to me as an outside viewer what you really want to extract.

[ocm]

rpeare,

Thank you for your help and hope the attaced screenshot (3 pages) further explains and answers your questions.


please let me know if this is what you are asking.


Regards,

[June7]

I think rpeare is asking for a spreadsheet with data, not descriptions about the data.

Maybe this will help you get your db design together http://www.rogersaccesslibrary.com/

[rpeare]

Yeah.. I'm not getting through.

I need an example of your spreadsheet with ACTUAL sample data (AS IT IS NOW), not descriptions of the data. You also keep talking about a CSV but you give no example of what you want your CSV to show (order of fields etc).

Are we assume for the sake of argument that the fields shown on your 'blank' form and your 'populated' form (pages 1 and 2) contain everything you want to saved in your Access database? Are these items then also sent to your csv, and it's all filled out by the user on the Access side?

Have you got a specific format for your CSV because if all we're talking about is exporting a specific record or records from a single table this problem is quite easy to solve but your page 3 is throwing me because I have no idea what it's supposed to indicate, is it supposed to show the order of the fields when you export them to your csv? because it's definitely not a csv format but you say it's what you 'want it to look like' but if that's coming out of your 3rd party software what has it got to do with you Access side problem?

[ocm]

June7
Thanks for the link I found it to be a good resource.

rpeare,

Thank you, as I was trying to formulate my reply, I saw your post. Your second paragraph sums it up and it’s what I try to accomplish.

Are we assume for the sake of argument that the fields shown on your 'blank' form and your 'populated' form (pages 1 and 2) contain everything you want to saved in your Access database? Yes
Are these items then also sent to your csv, and it's all filled out by the user on the Access side? Yes

I’ll then worry about the third part later.

Regards,

[rpeare]

arg.... one more try!

SO if you are currently exporting roughly 100 rows of data from an excel spreadsheet to a .csv how are you differentiating fields within the CSV?

for instance let's say you have 20 fields with the values 1 through 20 and you are putting 2 values in each 'field' of the .csv, are items space delimited within each comma separated value like:

1 2,3 4,5 6,7 8,9 10,11 12,13 14,15 16,17 18,19 20

or do they have some other formatting within each comma separated value? None of the stuff you've loaded explains the format of the csv and how you're 'splitting' fields within each discrete comma separated value.

[ocm]

Good questions and I think we are in the right direction.

The formatting and/or the splitting was done in Excel (my original sample attachment. As you noted, I entered multiple rows of data in one cell. For instance, I used Alt + enter multiple time to get two spaces etc. I’ll the save it as CSV to export. To answer your question, I do not do anything within the CSV.

I hope this helps,

TIA,

Regards,

[rpeare]

a comma separated file (csv) with a carriage return to mark individual fields within a larger concantenated field would look something like

1
2,
3
4,
5
6,
7
8,
etc.

is that how your current export looks? (again this is why I wanted an example of what your CURRENT end product looks like but still don't know)

How about answering this

let's assume for the sake of argument that YOUR DATASET is exactly what I've said, 20 fields and you want to put 2 fields per value in your CSV

show me what your CSV is supposed to look like the end product. If you can't supply the former, perhaps you can supply this otherwise I'm at a loss because your explanation isn't jiving with me.

[ocm]

Rpeare,

Attached is my CSV that you asked. As you can see my database is limited to 10 fields (below).

Field Name
Detail Info.
Technical Info.
Public
Source
Synonyms
Department
Data System
Time Context
Tag List

I hope this helps.

Regards,

[rpeare]

So you are only exporting 10 fields of a record that can be more than 10 data points, that's completely different than what you were saying

If you only want to export a portion of the data points you just create a query that has the 10 points you want then export the query instead of the table.

If you are saying, on the other hand, that let's say, again, that you have 20 fields with the contents 1 through 20 and you want to export them to a csv where the first row would be items 1 through 10 and the second row would be items 11 through 20 that's a different thing entirely because you do not have multiple fields in a single cell in your example csv.

[ocm]

Correct, I’m only exporting 10 fields.

TIA,