Jump to content


 


Register a free account to unlock additional features at BleepingComputer.com
Welcome to BleepingComputer, a free community where people like yourself come together to discuss and learn how to use their computers. Using the site is easy and fun. As a guest, you can browse and view the various discussions in the forums, but can not create a new topic or reply to an existing one unless you are logged in. Other benefits of registering an account are subscribing to topics and forums, creating a blog, and having no ads shown anywhere on the site.


Click here to Register a free account now! or read our Welcome Guide to learn how to use this site.

Photo

Porting csv file!!


  • Please log in to reply
11 replies to this topic

#1 gupta.vijay

gupta.vijay

  • Members
  • 28 posts
  • OFFLINE
  •  
  • Local time:06:58 AM

Posted 25 November 2010 - 02:42 AM

I recently bought android phone so I was transferring all my dat from my old phone to the new phone. On my old phone, I was using an application to track down my expenses that allows to export the csv file of the data that I have saved. Now, on the android phone, I find a separate application for the same purpose. It also allows exporting and importing of csv files. I am trying to import the csv file generated from my old phone to the new android phone. I am not able to do this as the format of the csv files for both the applications are different. I am posting both the files for old and new. Can anyone please let me know the procedure to change the format of the old csv file so that the format is supported by the new application. Please note that both the files look identical (also the formatting selected for the cells is same) when opened in MS Excel whereas the contents look different when opened in Notepad. I am pasting the example row from each file when opened in Notepad below: Example row from application in old phone 3/13/2010 11:58,Atm,Citibank,600 Example row from application in new phone "03-03-2010 09:49","Bus","test",-43 Now, I can change the format manually by opening the old file in Notepad but since there are hundreds of rows in the file, it is very tedious to do this manually. Can I attach csv files in this forum?

BC AdBot (Login to Remove)

 


#2 trebormuhaha

trebormuhaha

  • Members
  • 28 posts
  • OFFLINE
  •  
  • Local time:11:28 AM

Posted 25 November 2010 - 02:56 AM

Use the search and replace function of notepad

leave the quotation marks out and just replace the / with - and test pls could very well still work as they maybe ignored

kk if they aren't ignored search for , and replace with ","

if you need to add the 00/00/0000 date format you will need to use a spread sheet and open the file and format that Column to the correct date format

Also from the second sample you have i can't work out if the formating of the date is dd/mm/yyyy or mm/dd/yyyy if you need to change this you will require a macro as well let me know how you go i could probably make one or find one that will do the job pretty quick

adding the - to the last column is doing my head in can only do it with a macro as far as i can work out

!!!!!!!! last thing do not upload your csv files as they contain all of those peoples number and information

Edited by trebormuhaha, 25 November 2010 - 03:36 AM.

Best things since sliced bread

Download UBCD here
UBCD

Trend Micro House Call make sure those nasty little buggers are gone
Click here

#3 Animal

Animal

    Bleepin' Animinion


  • Site Admin
  • 35,512 posts
  • OFFLINE
  •  
  • Gender:Male
  • Location:Where You Least Expect Me To Be
  • Local time:06:28 PM

Posted 25 November 2010 - 06:06 PM

Can I attach csv files in this forum?

.csv files are not a recognized format for this forum. As noted you would not want to attach them anyway due to the information within the file.

The Internet is so big, so powerful and pointless that for some people it is a complete substitute for life.
Andrew Brown (1938-1994)


A learning experience is one of those things that say, "You know that thing you just did? Don't do that." Douglas Adams (1952-2001)


"Imagination is more important than knowledge. Knowledge is limited. Imagination circles the world." Albert Einstein (1879-1955)


Follow BleepingComputer on: Facebook | Twitter | Google+

#4 gupta.vijay

gupta.vijay
  • Topic Starter

  • Members
  • 28 posts
  • OFFLINE
  •  
  • Local time:06:58 AM

Posted 26 November 2010 - 01:26 AM

hi trebormuhaha,
i dont know if there is any search and replace functionality present in notepad.....are you sure that you mean to say notepad?

#5 myrti

myrti

    Sillyberry


  • Malware Study Hall Admin
  • 33,772 posts
  • OFFLINE
  •  
  • Gender:Female
  • Location:At home
  • Local time:03:28 AM

Posted 26 November 2010 - 09:04 AM

Open Notepad, click on Edit, click on Replace. Or use a different editor of your choice.

regards myrti

is that a bird?  a plane? nooo it's the flying blueberry!

If I have been helping you and haven't replied in 2 days, feel free to shoot me a PM! Please don't send help request via PM, unless I am already helping you. Use the forums!

 

Follow BleepingComputer on: Facebook | Twitter | Google+


#6 gupta.vijay

gupta.vijay
  • Topic Starter

  • Members
  • 28 posts
  • OFFLINE
  •  
  • Local time:06:58 AM

Posted 28 November 2010 - 07:03 AM

thanks for the reply....

but the query is still not completely resolved.

i want the automated method to convert the following statement

3 /13/ 2010 11:58 ,Atm, Citibank,600

to

"03 -03- 2010 09:49 "," Bus", "test " ,-43

but using the suggested method, that statement can be converted to the following statement

03 -03- 2010 09:49 "," Bus", "test " ,"-43

i still want an automated way to append a quotation mark before the first letter in each row (before 03 in this case) and remove the quotation mark after the last comma in each row (before -43 in this case)

#7 myrti

myrti

    Sillyberry


  • Malware Study Hall Admin
  • 33,772 posts
  • OFFLINE
  •  
  • Gender:Female
  • Location:At home
  • Local time:03:28 AM

Posted 30 November 2010 - 10:22 AM

Download awk: link

Install it into the default path. Then go C:\Program Files\GnuWin32\bin and copy awk.exe into your windows folder.

Open Notepad and copy/paste the code box below into a new text file.
@echo off
awk -F"," "{print \" \\\"\",$1,\"\\\",\\\"\",$2,\"\\\",\\\"\",$3,\"\\\",\",$4 }" "full path to input file" > "%userprofile%\Desktop\export.txt" 
"%userprofile%\Desktop\export.txt"
  • Replace "full path to input file" with the full path for the input file.
  • Save the file as uery.bat by choosing save as *All Files, and save it to your Desktop.
  • Locate "query.bat" and double-click on it to run. (It is important that you run the script from the drive where your operating system is installed).
  • It will open a text file, it should contain the data as you want to have it..

Edited by myrti, 30 November 2010 - 10:55 AM.

is that a bird?  a plane? nooo it's the flying blueberry!

If I have been helping you and haven't replied in 2 days, feel free to shoot me a PM! Please don't send help request via PM, unless I am already helping you. Use the forums!

 

Follow BleepingComputer on: Facebook | Twitter | Google+


#8 gupta.vijay

gupta.vijay
  • Topic Starter

  • Members
  • 28 posts
  • OFFLINE
  •  
  • Local time:06:58 AM

Posted 30 November 2010 - 12:16 PM

thanks myrti for the reply. when you say windows, do you mean to say windows 32 folder that is in documents and settings folder (dont remember the exact path). if not, can you please provide the full path.

i got confused by the statement " It is important
that you run the script from the
drive where your operating system is installed". can i run that query.bat from the desktop as it would be c: and that's the drive where my operating system is installed?
i was confused as every user would be running query.dat from the drive from where operating system is installed if the user is saving the file on desktop. please correct me if i am incorrect?

#9 myrti

myrti

    Sillyberry


  • Malware Study Hall Admin
  • 33,772 posts
  • OFFLINE
  •  
  • Gender:Female
  • Location:At home
  • Local time:03:28 AM

Posted 01 December 2010 - 04:40 AM

Hi,

if your Desktop is on the same disk as your windows partition, then you can just run the batch from your desktop. However there are people that set up their PC so that they have their userprofile on a different partition than their windows installation. Those would have to run the batch from a different place than Deskop.

I meant this folder: C:\windows. Can you please give me the exact path to the folder you were referring to?

regards myrti

is that a bird?  a plane? nooo it's the flying blueberry!

If I have been helping you and haven't replied in 2 days, feel free to shoot me a PM! Please don't send help request via PM, unless I am already helping you. Use the forums!

 

Follow BleepingComputer on: Facebook | Twitter | Google+


#10 gupta.vijay

gupta.vijay
  • Topic Starter

  • Members
  • 28 posts
  • OFFLINE
  •  
  • Local time:06:58 AM

Posted 01 December 2010 - 06:19 AM

thanks myrti for the reply!! i was referring to the same folder...i will do it and let you know if it works.

#11 gupta.vijay

gupta.vijay
  • Topic Starter

  • Members
  • 28 posts
  • OFFLINE
  •  
  • Local time:06:58 AM

Posted 09 December 2010 - 07:00 AM

sorry myrti for the late reply. issue got resolved before i could try your suggestion but many thanks for the reply.

#12 JosiahK

JosiahK

  • Members
  • 269 posts
  • OFFLINE
  •  
  • Gender:Male
  • Local time:02:28 AM

Posted 16 December 2010 - 12:53 PM

For the record in case anyone else has the problem, OpenOffice.org Calc will save CSV files in the format
"3 /13/ 2010 11:58 ","ATM","Citibank",600
Quod non mortiferum, fortiorem me facit.
I don't read minds. Please help everyone by answering any questions and reporting on the results of any instructions. Query any concerns and explain problems or complications.




0 user(s) are reading this topic

0 members, 0 guests, 0 anonymous users