Changing default data format settings when importing CSV files

[kewell12]

Hi, we work with many data files which are saved as CSV files. In some of our
files, the data values are numbers which themselves are comma seperated but
they are encapsulated within text quotes e.g. "25,99999".

When I double click the CSV file and Excel automatically loads the file, the
number format gets changed to 2,599,999 even though the original value was in
a text string. I know I can solve this problem using the import wizard but
when you are working with 30-50 of these files per day I do not want to use
the import wizard all the time.

does anyone have any ideas if I can change the default formatting of the
number that excel is applying?

Thanks!

 

[Murray]

Kewell12

Don't know that there is a way to change the formatting, but a macro
could do it.

Put this code in a macro module and see if it works (warning:
untested!)

Sub LoadFile()
Dim FName As String
FName = Application.GetOpenFilename(filefilter:="All Files (*.*),
*.*")
Workbooks.OpenText Filename:=FName, Origin:=xlMSDOS, _
StartRow:=1, DataType:=xlDelimited,
TextQualifier:=xlDoubleQuote, _
ConsecutiveDelimiter:=False, Tab:=False, Semicolon:=False,
Comma:=True _
, Space:=False, Other:=True, OtherChar:="""",
FieldInfo:=Array(Array(1, _
1), Array(2, 1), Array(3, 1), Array(4, 1)),
TrailingMinusNumbers:=True
End Sub

Regards

Murray

 

[Dave Peterson]

If you open a .CSV file, excel will do what it wants.

But you can rename it to .txt, then use File|Open to open it.

You'll see the text import wizard and you can specify each of the fields
(general, date, text, skip).

If you do it lots of times for text files with the same format, you can record a
macro that makes life easier.

Saved from a previous post:

I like to create a dedicated macro workbook that contains the code. And then I
put a big button from the Forms toolbar on the only worksheet in that workbook.
I'll add a few instructions to that sheet, too.

Then I can distribute this macro workbook to other users (and use it myself) so
that I can just click the giant button to invoke the macro that imports the text
file.

I'd tweak the code to get the name of the file to open from the user and then
include code that adds some more stuff--like formatting, filters, subtotals,
page setup (headers/footers/rows to repeat at top/etc).

Then it actually becomes a tool that makes life a lot easier.

My tweaked code could look a little like:

Option Explicit
Sub Testme01()

Dim myFileName As Variant

myFileName = Application.GetOpenFilename(filefilter:="Text Files, *.Txt", _
Title:="Pick a File")

If myFileName = False Then
MsgBox "Ok, try later" 'user hit cancel
Exit Sub
End If

Workbooks.OpenText Filename:=myFileName '....rest of recorded code here!

End Sub

 

[kewell12]

Thanks very much Murray, I'll give this a go

Kewell12

Don't know that there is a way to change the formatting, but a macro
could do it.

Put this code in a macro module and see if it works (warning:
untested!)

Sub LoadFile()
Dim FName As String
FName = Application.GetOpenFilename(filefilter:="All Files (*.*),
*.*")
Workbooks.OpenText Filename:=FName, Origin:=xlMSDOS, _
StartRow:=1, DataType:=xlDelimited,
TextQualifier:=xlDoubleQuote, _
ConsecutiveDelimiter:=False, Tab:=False, Semicolon:=False,
Comma:=True _
, Space:=False, Other:=True, OtherChar:="""",
FieldInfo:=Array(Array(1, _
1), Array(2, 1), Array(3, 1), Array(4, 1)),
TrailingMinusNumbers:=True
End Sub

Regards

Murray

 

[kewell12]

Thanks Dave, I'll have to see if that works for our processes. Some of the
people can be a bit rigid with change.