Creating a CSV files from a excel sheet

[exceljob]

Hi There
I am new to Excel/VBA world. I have a job to do which is described below:

I have to take a excel spreadsheet and convert it into a specially created CSV file format that can be used with the PLC. The excel spreadsheet info is below:

The excel spreadsheet will contain the informatoin about all the points (user will be filling it manually) for example
COL A (description) "Water Level High"
COL B (Identifier, user selectable, 1 for DI, 2 for DO etc) in the above example it will be DI (which is 1)
COL C (Description) "This is water level high high"

There should be a button which will run the code on the back that will not only create a csv file in the following format (coloumns), but also will take the above information and fill the newly created csv file:

TYPE SCOPE NAME DESCRIPTION DATATYPE SPECIFIER ATTRIBUTES

For example, in the above example "Water Level high" will go to the "NAME" column of the CSV file, the identifier "DI" will become the "DATATYPE" in the csv file and the description column will go to the "Description"field in csv file. "Type" and "SCOPE" fields will be hard coded based on the identifier selected. If you guys can give me some example code or a kind of headsatrt it will be of great help. Thanks in advance

Regards

 

[SkipVought]

Hi,

"...CSV file format that can be used with the PLC..."

Are you certain that you want a COMMA separated file? Might you really want a FIXED WIDTH FILE, where each column of data is in a specific column range in the file?

.csv file
[tt]
SkipVought,2001,5
SomeOtherGuy,2005,735
[/tt]
Fixed Width
[tt]
SkipVought 200100005
SomeOtherGuy 200500735
[/tt]


Skip,

_{To be safe on the [red]FOURTH[/red],
Don't take a [red]FIFTH[/red] on the [red]THIRD[/red]
Or you might not come [red]FORTH[/red] on the [red]FIFTH[/red]
[red][highlight blue]FORTH[/highlight][/red][white][highlight red]WITH[/highlight][/white]}

 

[xlbo]

might be missing something here but

File>Save As > choose csv

Rgds, Geoff

We could learn a lot from crayons. Some are sharp, some are pretty and some are dull. Some have weird names and all are different colours but they all live in the same box.

Please read FAQ222-2244 before you ask a question

 

[SkipVought]

Geoff, my thots,exactly.

So is the question,

how to create a .csv or

how to map and format the data.


Skip,

_{To be safe on the [red]FOURTH[/red],
Don't take a [red]FIFTH[/red] on the [red]THIRD[/red]
Or you might not come [red]FORTH[/red] on the [red]FIFTH[/red]
[red][highlight blue]FORTH[/highlight][/red][white][highlight red]WITH[/highlight][/white]}

 

[exceljob]

I exported my PLC tag database into csv format, I can use that format as a template and create the CSV file of that template, inthis way i am sure that i have same formatting of csv file which PLC had created. But how to do that have no clue?

 

[xlbo]

I have to take a excel spreadsheet and convert it into a specially created CSV file format that can be used with the PLC

To convert FROM excel TO csv, simply do a SAVEAS and choose the csv option

If you need to convert data BEFORE creating the csv from excel, please xplain what data needs to be converted and HOW

Rgds, Geoff

We could learn a lot from crayons. Some are sharp, some are pretty and some are dull. Some have weird names and all are different colours but they all live in the same box.

Please read FAQ222-2244 before you ask a question

 

[exceljob]

YES I NEED TO CONVERT THE DATA BEFORE I CREATE THE CSV FILE. hOW TO CONVERT THE DATA I HAVE EXPLAINED IT IN MY ORIGINAL MESSAGES, WHICH I AM GOING TO QUOTE IT BELOW:

"The excel spreadsheet will contain the informatoin about all the points (user will be filling it manually) for example
COL A (description) "Water Level High"
COL B (Identifier, user selectable, 1 for DI, 2 for DO etc) in the above example it will be DI (which is 1)
COL C (Description) "This is water level high high"

There should be a button which will run the code on the back that will not only create a csv file in the following format (coloumns), but also will take the above information and fill the newly created csv file:

TYPE SCOPE NAME DESCRIPTION DATATYPE SPECIFIER ATTRIBUTES

For example, in the above example "Water Level high" will go to the "NAME" column of the CSV file, the identifier "DI" will become the "DATATYPE" in the csv file and the description column will go to the "Description"field in csv file. "Type" and "SCOPE" fields will be hard coded based on the identifier selected. If you guys can give me some example code or a kind of headsatrt it will be of great help. Thanks in advance

 

[SkipVought]

So the question is NOT how to create a .csv.

The question is how to bring your data together in a certain manner.

Please be clear and specific.

What code do you have so far?

ALSO, please post a sample of the SOURCE data.

Skip,

_{To be safe on the [red]FOURTH[/red],
Don't take a [red]FIFTH[/red] on the [red]THIRD[/red]
Or you might not come [red]FORTH[/red] on the [red]FIFTH[/red]
[red][highlight blue]FORTH[/highlight][/red][white][highlight red]WITH[/highlight][/white]}

 

[exceljob]

LIKE I SAID BEFORE, I AM TOTALLY NEW TO THIS FEILD AND I HAVE NO CLUE HOW THIS THING WOULD BE IMPLEMENTED? HENCEI HAVE NOTHING WITH ME. IF YOU GUYS GIVE ME SOME EXAMPLES AND KIND A HEAD START, I WILL TRY TO BUILD SOMETHING ON TO IT.....I REALLY APPRECIATE YOUR HELP IN THIS REGARD

 

[xlbo]

ok

1: stop SHOUTING - there is a difference between using CAPS to highlight a point and just being plain rude

2: You seem to think that you need to manipulate the data INTO a csv file. The point myself and Skip have been trying to make is that all you need to do is set up the excel file as you want the csv and then save it as a csv file

3: What exactly is the issue you are having. What have you tried that doesn't work? So far, all you have done is demand help on something that you have not fully described. You say that: "Type" and "SCOPE" fields will be hard coded based on the identifier selected but neglect to supply an info on what that would be. You havn't even mentioned where the SPECIFIER and ATTRIBUTES data would be coming from

To be honest, this sounds like all it needs is a re-ordering of a couple of columns, change the column headers and use some formulae to lookup the TYPE & SCOPE

Let me see if I can bring some clarity here:

a: You currently have an EXCEL file with the following data in it
ColA: Description, ColB: ID, ColC: Another Description

b: You want to put this in a CSV file with the headings of:
TYPE SCOPE NAME DESCRIPTION DATATYPE SPECIFIER ATTRIBUTES
where ColA maps to NAME, colB maps to DATATYPE & colC maps to DESCRIPTION

I have no idea where the data for the rest of the columns is going to come from but for now I suggest renaming the column headers in the excel sheet as per the mapping and insert columns to create the layout you desire. Populate the rest of the data and save as csv. simple as.


Rgds, Geoff

We could learn a lot from crayons. Some are sharp, some are pretty and some are dull. Some have weird names and all are different colours but they all live in the same box.

Please read FAQ222-2244 before you ask a question

 

[xlbo]

To find out how to code, the macro recorder is your friend as is the help file.

You need to re-order some columns - therfore, go Tools>MAcros>Record new macro. Whilst that is running, cut and paste columns into the positions you require. Stop the macro recorder and look at the code. This is how to move columns of data about laterally.

You may also record yourself saving the excel file as csv - this will generate code to do that action

The rest of your request, I can give you no help with as you have not described how the data is obtained.

Rgds, Geoff

We could learn a lot from crayons. Some are sharp, some are pretty and some are dull. Some have weird names and all are different colours but they all live in the same box.

Please read FAQ222-2244 before you ask a question

 

[Bong]

There must be more here than meets the eye, since not much is meeting the eye. So lets assume that SaveAs is not going to work for you; for some reason you cannot make the spreadsheet conform to the layout of the CSV file.

A CSV file is a text file, on each line of which is an equal number of commas between and around which are some data. Consider this:
1. Open a text file for writing (call it something.csv)
2. dimension an array for the correct number of values that will be in the CSV file.
3. populate those elements of the array corresponding to the values in the CSV file that will not come from the spreadsheet.
4. march down the rows of your spreadsheet, on each row, populate the appropriate array elements.
5. using join(<arrayname>, ","), make a comma separated string from the array
6. print out the string to the text file
7. continue
8. close the text file

_________________
Bob Rashkin

 

[exceljob]

Hi Geff
I am sorry if you think that i was being rude,i was just trying to explain the things (which i might not be able to explain properly due to my less knowledge in Excel/VBA).....Ok i try it one more time:

All i am trying to do is to take the raw information in excel sheet and manipulate this information in a specific way (csv format) so that i can create the plc tags for my PLC job. Now this csv format that i describe above is the format PLC accepts. The "SPECIFIER" and "ATTRIBUTE" fields are not that important and can be left blank. But the "TYPE" and "SCOPE" fields are more important. The TYPE field describes the kind of tag i want to create, for example either its a BASE TAG or ALIAS tag, this information can be determined from the ID field of the excel sheet. Lets say if ID field is 1 then TYPE field contains "TAG" string else TYPE field contains "ALIAS" string. Similary the SCOPE field is determined the scope of the tag i would like to create (NAME field in the Excel sheet). Is it controller based tag or a program based tag, if ID field is 1 then SCOPE field will contain "CONTROLLER" string else it will contain "PROGRAM" string. I hope this helps you understand what i am trying to get. thanks in advance

 

[SkipVought]

Do you have a sample of code that you are having trouble with?

Please remember, the is Tek-Tips not [red]rent-a-coder-for-nuthin[/red].

Skip,

_{To be safe on the [red]FOURTH[/red],
Don't take a [red]FIFTH[/red] on the [red]THIRD[/red]
Or you might not come [red]FORTH[/red] on the [red]FIFTH[/red]
[red][highlight blue]FORTH[/highlight][/red][white][highlight red]WITH[/highlight][/white]}

 

[xlbo]

ok - I think you are thinking this is more complicated than it is.

Step 1

Create headers as you want them in a new excel workbook

Step 2

Copy data from your current excel file to the appropriate column in the new file (description / ID / Description

Step 3

You can use a formula to populate your other data columns using an IF statment

Once you have the data in excel inm the format that you need it in, all you need to do is do a SaveAs and choose csv as the save type. This will save the data as per the layout in excel, in csv format.

Rgds, Geoff

We could learn a lot from crayons. Some are sharp, some are pretty and some are dull. Some have weird names and all are different colours but they all live in the same box.

Please read FAQ222-2244 before you ask a question