Free Microsoft Excel 2013 Quick Reference

CSV vs Excel

Does a csv file hold more lines of data than a regular excel file?
I had been under the impression it can hold more lines, and when you open it up you will see more lines, not that it get's converted back to excel.


How do you set default column width when opening a *.csv in Excel?
Changing files in XlStart directory did not help.

-nn

My coworker saved an excel file as a csv and sent it to me to play around with. However, for some reason he managed to save the csv without any commas in it. Therefore, when I try to import it into excel all of the data goes into the first column mashed up together in the first column. I will attach the csv file. When you open up the csv (through right clicking and hit edit) you can see the columns of data as they are supposed to appear in excel. Is there any way to get this data into excel? Thanks for your help in advance!

-Ethankjkj.txt

Note: Had to attach as a txt because can't attach a csv.

Hi

I have tried using the macro recorder to import a csv file into an Excel workbook. The csv file name does not change, but the excel filename changes each day (it is saved with todays date at the end)

eg file01oct.xls, file02oct.xls, file03oct.xls, etc

Using the macro recorder, it imports succesfully on day one, but the next day, as soon as the excel file is renamed, the macro fails. It cannot recognise the change in the excel file name.

Please can you help me fix the macro so it can hope with the change in the excel filename each day?

Code provided below.

Many thanks


	VB:
	
Sheets("MORCOM").Select 
Range("A5").Select 
Cells.Select 
Selection.ClearContents 
Range("A1").Select 
Workbooks.Open Filename:= _ 
"C:fo_today_position_detail.csv" 
Cells.Select 
Selection.Copy 
Windows("Statement 02112011.xls").Activate 
Cells.Select 
ActiveSheet.Paste 
Range("G6").Select 
Windows("fo_today_position_detail.csv").Activate 
ActiveWindow.Close 
Sheets(" MORCOM").Select 
Range("A2").Select 

If you like these VB formatting tags please consider sponsoring the author in support of injured Royal Marines


Could someone please help me compare flat files vs excel spreadsheets? I am looking for Pros and Cons.

Thanks!

Using winxp home ed SP2 and Office 2000.

When receiving a csv from a web source, IE6 automatically hooks you to excel. Problem is, excel doesn't receive it correctly. It all comes into the first cell of each row, ignoring the commas. Excel seems to treat the data stream as a set of strings, not csv.

Then when telling excel to save the received data as a csv, the result from excel is a quoted string for each row, with quotes of course added to any already embedded quoted data. Now I have quoted strings with double quoted embedded strings. Ok for text file but was supposed to be a csv. Importing again to excel as either text or csv, there is no way to get rid of the enclosing quotes easily and it's still interpreted incorrectly by excel. ( and access and anything else MS)

So, I went to file explorer and changed the association of csv from excel to notepad, then went to the web and downloaded the csv again. Same result, no difference. I thought that would disconnect excel from IE6. I really wanted the file to be saved directly without excel getting in the way. There is no option on the web site to save the data as a file (javascript button not a data link, that's why)

Finally, I went into the registry and went to HKLM/Software/Classes/.csv and deleted the primary key Data Value. (It was: Excel.CSV and I simply erased it.) Of course I left the key structure in place.

Went to the web again to get the csv data, and this time a file save dialog came up for the csv file, which was what I really wanted anyhow. This must have been done by XP due to missing excel hook to csv. Finally was able to disconnect excel csv from IE6. I saved the data, opened in notepad and inspected. It is a perfect csv file.

I opened excel and imported the saved csv file using the defaults from excel and it loaded 100 percent correctly!!!

Q: Why didn't excel read it correctly from the web?

Q: Why didn't changing the file association to notepad remove or change the csv class key value?

Q: Is there a fix you're aware of for excel 2K to read a csv structured web data stream correctly? Is there some obscure excel or IE6 setting or something I need to try?

Of course I could easily write a script to fix the excel-hosed version of the streamed-in csv, but that's not the point. Excel should understand the incoming data the first time.

Tx for your advice.

Robert Phillips

If somebody knows, please help me.
I really appreciate your help.

The previous programmer created an excel template file(.xlt) in lower version.

If we save this template file as [CSV (Comma delimited)(*.csv)] format in
(Excel 97-2003), the delimited comma will be until the last column that has
the data.

But if we save this template file as [CSV (Comma delimited)(*.csv)] format
in (Excel 2007), the delimited comma will be until the column at the end of
the sheet.

How can I do to be exactly the same as the lower versions (Excel 97-2003)
without changing the template file?(To be compactable with the older versions
of our product, we are not able to recreate the template and we want to use
the same template file.)

Please see the attached files.

http://www.4shared.com/file/32023208/2dc0346d/Data.html

Thanking you in advance,

Khayae
S'pore

Is there a workaround to this issue yet? I see no option to specify
the character encoding of a CSV file when it is being opened. I've
seen several posts in this group about this issue over the last few
years, with no resolution. There seems to be no useful knowledge base
information either.

Using the "Data/Get External Data/Import Text File..." menu does not
work either, as the list of encodings it gives is limited to a sorry
list of Macintosh, Windows, or DOS.

Help?

[Ironically, I've been using Open Office to do this with no trouble. I
was going to switch to Excel because it's faster, but this is a deal
breaker.]

Thanks.

The previous programmer created an excel template file(.xlt) in lower version.

If we save this template file as [CSV (Comma delimited)(*.csv)] format in
(Excel 97-2003), the delimited comma will be until the last column that has
the data.

But if we save this template file as [CSV (Comma delimited)(*.csv)] format
in (Excel 2007), the delimited comma will be until the column at the end of
the sheet.

How can I do to be exactly the same as the lower versions (Excel 97-2003)?

Please see the attached files.

http://www.4shared.com/file/32023208/2dc0346d/Data.html

Thanking you in advance!

Khayae

I have an Excel file that contains Romanian characters (my computer is UK
configured). When I save the file as .csv from Excel and open the new csv
file in Notepad the foreign characters are not displayed correctly. Can
anyone help?

Thanks
Mark

Regardless of the list separator value in regional settings, when
double-clicking the csv file, Excel 97 opens the file using "," as the
delimiter. Otherwise, when opening the file via File>Open in Excel or using
open with...Excel, the regional settings are not ignored. Is there a reason
for this? Thanks in advance.

Clicking Save As in Excel 2002 does not give me the option to save as CSV so
I guess I have to download that ability but I can't find it.

Basically I want to convert a column of words in Excel to a text list with
commas in between without tabs or paragraphs.

Thanks,
BrianH

I have output from an application that I wish to view in excel (office 2000).
This output is in CSV format.

When I open the CSV file, excel puts all the text into a single cell instead
of into multiple cells on multiple lines.

The file contains the following text as part of a field:

The SQL statements should read ""INSERT INTO (,
, ) VALUES (, ,
);""

It appears that text between angle brackets () is being interpreted by
excel as HTML tags (if this is part of a large file, then an HTML error is
generated).
If I replace all "

How can I convert many .csv files to excel? Is there a certain macro or tool maybe.
thx,
mrggutz

Hi,

I want to import a CSV file to an Excel file automatically. The CSV file is located on 'D:MyData' and the filename called 'Data1.CSV'.

The Excel filename is 'E-Data1.xls'. After open the Excel file it should erase previous imported data and import the data from the latest CSV file, then it should auto save and close the Excel file.

The imported data is save under the Excel worksheet called 'row_data'

The Flow should be like this.

1) Delete previous imported data(data from previous CSV file)
2) Import data from CSV file to Excel worksheet 'row_data'.
3) Auto save the excel file 'E-Data1.xls'.
4) Close the Excel file.

Appreciate if you can help me.

Thanks
Kee

Hi, Iam a begginner in macros.
It will be great if somebody can help me in importing csv files to different work sheets in one excel file using macros.
In my case there will be 4 csv file generated from a programm every day and i want to import these files in an excel file to extract some data. The next day i should be able to select the csv files to import and replace the old csv files in the excel file.

Thanks in advance.

This is a little convoluted, so forgive me if I take this step-by-step.

I have a large database of information that I have gotten a CSV file for.

In that file there are leading zeros, so I have to import the CSV file so I can tell it to import as text.

That file also contains some data fields that contain line-breaks of some sort. I am not sure what kind.

When I sure the import function, I believe EXCEL is seeing the breaks as 'row-deliminators' and breaking up the data at 'bad' points.

If I just Open the file directly, I do not have the problem with line breaks (all rows line up properly) BUT then I lose my leading zeros.

PLEASE can someone tell me if there is a way to do both these things: Import data with leading zeros, and properly have the rows break?

(The CSV is comma deliminated with " as a text delimiter)

Thank you in advance for any help you can provide.

On my development machine VB6 application creates Excel 2003 worksheet and
saves the following fine:
xlSheet.SaveAs FileName:="C:AuctionNandB.csv",
FileFormat:=Excel.XlFileFormat.xlCSV, CreateBackup:=False

On Users machine my application gpfs but creates Excel 2000 worksheet.
Grateful for any pointers

Hello to all,

I have a question regarding CVS import. I need to do two tasks. First I
need to select a range in a worksheet (could be 4 rows by 4 cols or
more) and then create a CSV file with this range. I have about 30 such
kinds of files. So I create 30 CSV files.

Then what I need to do is, then import this data into another main
excel sheet. I will need to put the rows/cols where to put the data.

Hence I need two answers actually. The first would be to create the CSV
file from the excel sheet (selected range) and then the second solution
required to get the data back into another excel sheet.

Any help, pointers and directions will be highly appreciated.

Regds

I.m trying to import from a .csv file with 8 columns of data into an excel
spreadsheet. The positions in the .csv string have to be shuffled around a
little. For example, string position 1 needs to import to column 5 in excel.
I would be grateful for some help with the code.

Many thanks

Hi,

I've written a small VB application to convert a CSV file into an Excel
file but some fields in the resulting Excel file seem to be truncated
at 256 characters while others aren't.

This also happens if I import the file into Excel manually.

Does anyone know how I can work around this?

Thanks in advance, let me know if you need any more information..

Steven Davies

Hi folks...

I know this is not a development forum but I need some Excel help...

I am developing a application ("Application Out" we will call it) which generates a CSV file (Gameplan.csv) from an SQL query. This CSV file is then being taken in by another application ("Application In" we will call it) which then uses it as its starting data, but that application is functioning normally.

If we try to load the CSV straight from my app("Application Out") into the other application("Application In" ), it will not accept it. But if we open the CSV in Excel and change nothing (literally open and close it again) we get the following error/save prompt -

"Gameplan.csv may contain features that are not compatible with CSV (Comma delimited). Do you want to keep the workbook in this format?"

And we are prompted to save. If you then save this file as "GameplanNew.csv" for example and then go on to the second application, it will load without any error what so ever.

I have been experimenting with different encoding types but nothing seems to be working?

What could Excel be doing that is changing the file?

When I run a binary comparison, the file has significantly changed, so Excel is changing alot. But when the file is viewed in Excel or notepad, the file looks identical.

Can anyone offer any thoughts or where I should go to get more help?

Any help would be appeciated.

Many Thanks

Windows 2002 SP2, Office 2003 SP2

I am trying to avoid potential problems with a new process and would
appreciate your input.

Right now we have a vast amount of data downloaded into over 200 Excel
files, into a formatted style (no database-style lists - the output looks
like a finished report). We refresh the data every month. We then have
another template file with a macro that opens up each of these data files,
imports selected sheets of data, does a save as, and moves to the next data
file. We have several different computers simultaneously running different
templates through this process each month.

My data supplier tells me that they can speed up my data download time
(which is currently about 2 weeks across 5 PCs) if they can put the data into
a CSV file instead of straight into Excel.

I see a lot postings about problems converting from CSV to Excel...is there
anything I can do to avoid problems? Anything I need to ask my supplier?
What types of problems are typical? Should I just not even consider the idea?

Thanks for your insights!

Hello group,

I'm one of the many struggling with excel's csv-import functionality. After
reading through this group and trying out various things I'm not much wiser
so I try to ask for a solution.

I have an application that creates a cvs file for the user with some data.
One of the columns can contain data that looks like this: "12/76" -
unfortunately excel violates the cvs-spec and does interpret this as a
(incomplete) date. It then displays "01.12.1976" for example, which is just
plain wrong and must not happen.

I tried the trick with a macro that marks all cells and sets the cell-type
to "text". Now Excel utterly corrupts the data and displays some numeric
value, probably derived from the date. The transmitted data was "12/76" and
excel now has "28095", whatever that should mean. In any case, data got
corrupted and is unusable.

Excel imports the data correct if and only if I open excel with an empty
worksheet, use the data import wizard to select the csv file, select csv
mode, select the separator char, select the afflicted column and switching
its type from standard to text and finally confirm the import at cell A1.
This works and works correctly, but is far too many steps for the users
which are not computer professionals. Also this needs to be repeated for
all csv files of that type.

Additionally, the data is server generated within a web-application and
upon generation the user can open or save the csv file directly. Opening it
directly is the one convenient way but apparently does not work without
causing data corruption through excel.

I also read about some other tricks like putting a '-sign in front of the
column-data which also works, but alters the data which again must not
happen since this data has some specific meaning and there are other
programs working on the same csv files. Also copy&pasting the line
containing this workaround also copies this char with it.

I further tried in vain to redefine the "standard" cell formatting to be
the same as "text" but this doesn't work either.

So my question to the group is: Is there any way to prevent Excel to
assume/convert data from a csv file, while still allowing the csv file to
be opened by double-clicking it which opens/starts Excel or by sending a
server-generated csv file from the webserver ?

thanks in advance

René

--
-------------------- http://NewsReader.Com/ --------------------
Usenet Newsgroup Service $9.95/Month 30GB

Hi,
I would like to know, how to save the Doubly-Byte characters.For example,
Japanese in CSV of Excel.Here are the steps, I tried.
1. Open Excel
2. Copy pasted the Japanese charactesrs in a cell.It is displaying the
characters correctly.
3. Save as CSV(Comma seperated file)
It is saving as ??????.
Thanks in advance for the info.