Jump to content

Welcome to Geeks to Go - Register now for FREE

Geeks To Go is a helpful hub, where thousands of volunteer geeks quickly serve friendly answers and support. Check out the forums and get free advice from the experts. Register now to gain access to all of our features, it's FREE and only takes one minute. Once registered and logged in, you will be able to create topics, post replies to existing threads, give reputation to your fellow members, get your own private messenger, post status updates, manage your profile and so much more.

Create Account How it Works
Photo

Reformatting CSV data in Excel


  • Please log in to reply

#1
Professional Customer

Professional Customer

    Member

  • Member
  • PipPip
  • 11 posts

I want to transfer information from one program to another via CSV file. I have two problems with this process and I'm hoping there is one solution to fix both.

 

1. The CSV file created by the old program breaks the date into three columns; year, month, day. The new program needs the date in a properly formatted date field such as 'yyyy/mm/dd'. When I import the CSV file into Excel I can't seem to combine the three columns for the date into one column. There are well over a thousand entries so manually doing it (via cut and paste) is out of the question.

 

2. The second problem is basically the same as above. The old program saves blood pressure and pulse in three separate columns. I need to combine these three columns into one cell in the following format: dia/sys/pul where dia is the diastolic measure, sys is the systolic measure and pul is the pulse.

 

Any help with these two problems would be greatly appreciated.

 

 


  • 0

Advertisements


#2
SleepyDude

SleepyDude

    Trusted Helper

  • Malware Removal
  • 4,149 posts

Hi,

 

There is a free command line tool to manipulate text files called Swiss File Knife SFK that can do what you need very easily and fast.

 

If you need specific help for your case I can try to help if you post 5 or 10 lines of the CSV and the expected result.


  • 0

#3
Professional Customer

Professional Customer

    Member

  • Topic Starter
  • Member
  • PipPip
  • 11 posts

I tried to upload a spreadsheet but it said I didn't have premission to do that. Let's try this link:

 

http://joewest.ca/sp...ampleBPdata.xls

 

that works!


Edited by Professional Customer, 20 March 2015 - 01:51 PM.

  • 0

#4
SleepyDude

SleepyDude

    Trusted Helper

  • Malware Removal
  • 4,149 posts

Hi,

 

- Download the sfk.exe and save it on the same folder as the .csv file

- open the command prompt on the folder containing the two files (Tutorial)

- Copy & paste the following command:

sfk.exe +filter -spat -sep "," -form "$col8-$col7-$col6,$col9:$col10,,,,,,$col1/$col2/$col3,,," SampleBPdata.csv > Data.csv

The file Data.csv will have the data formatted, you will need to adjust the titles on first line and force Excel to format the columns as Date and Time when importing...


  • 0

#5
Professional Customer

Professional Customer

    Member

  • Topic Starter
  • Member
  • PipPip
  • 11 posts

Thanks SleepyDude. That worked perfectly. Sorry for taking so long to respond.


  • 0

#6
SleepyDude

SleepyDude

    Trusted Helper

  • Malware Removal
  • 4,149 posts

Thanks SleepyDude. That worked perfectly. Sorry for taking so long to respond.

 

You are welcome. Not at all.


  • 0






Similar Topics

0 user(s) are reading this topic

0 members, 0 guests, 0 anonymous users

As Featured On:

Microsoft Yahoo BBC MSN PC Magazine Washington Post HP