Home Menu

Menu


ExcelBanter » Excel Newsgroups » Excel Programming > Aplication to import and Convert Data
Reply
 
LinkBack Thread Tools Search this Thread Display Modes
  #1   Report Post  
Posted to microsoft.public.excel.programming
external usenet poster
  
Posts: 788
Default Aplication to import and Convert Data
I would like to create an application using MS Excel. Here is the tast at hand
I have a .csv file I need to import once that file is imported I need to
change one of the column so that the date would be mmddyy

Sample data of the .csv

Date 1 2 3 4
20051231 0.0446 0.0455 0.0461 0.0466
20051230 0.0445 0.0454 0.046 0.0465
20051229 0.0445 0.0453 0.0459 0.0465
20051228 0.0445 0.0453 0.0459 0.0464
20051227 0.0444 0.0453 0.0458 0.0464
20051226 0.0444 0.0453 0.0458 0.0464
20051225 0.0444 0.0453 0.0458 0.0464
20051224 0.0444 0.0453 0.0458 0.0464
20051223 0.0444 0.0453 0.0458 0.0464

Result I am looking to get

Date Term Rate
12/31/2005 1 4.46
12/30/2005 1 4.45
12/29/2005 1 4.45
12/28/2005 1 4.45
12/27/2005 1 4.44
12/26/2005 1 4.44
12/25/2005 1 4.44
12/24/2005 1 4.44
12/23/2005 1 4.44
12/31/2005 2 4.55
12/30/2005 2 4.54
12/29/2005 2 4.53
12/28/2005 2 4.53
12/27/2005 2 4.53
12/26/2005 2 4.53
12/25/2005 2 4.53
12/24/2005 2 4.53
12/23/2005 2 4.53
12/31/2005 3 4.61
12/30/2005 3 4.6
12/29/2005 3 4.59
12/28/2005 3 4.59
12/27/2005 3 4.58
12/26/2005 3 4.58
12/25/2005 3 4.58
12/24/2005 3 4.58
12/23/2005 3 4.58
12/31/2005 4 4.66
12/30/2005 4 4.65
12/29/2005 4 4.65
12/28/2005 4 4.64
12/27/2005 4 4.64
12/26/2005 4 4.64
12/25/2005 4 4.64
12/24/2005 4 4.64
12/23/2005 4 4.64

Reply With QuoteReply With Quote
  #2   Report Post  
Posted to microsoft.public.excel.programming
external usenet poster
  
Posts: 6,953
Default Aplication to import and Convert Data
You said a CSV file, so if your file actually looks like this:

20051231,0.0446,0.0455,0.0461,0.0466
20051230,0.0445,0.0454,0.0460,0.0465
20051229,0.0445,0.0453,0.0459,0.0465
20051228,0.0445,0.0453,0.0459,0.0464
20051227,0.0444,0.0453,0.0458,0.0464
20051226,0.0444,0.0453,0.0458,0.0464
20051225,0.0444,0.0453,0.0458,0.0464
20051224,0.0444,0.0453,0.0458,0.0464
20051223,0.0444,0.0453,0.0458,0.0464

then this will work:

Sub aBC()
Dim bk As Workbook, rng As Range
Dim s As String, y As String, m As String
Dim d As String, dt As Date, rng1 As Range
Set bk = Workbooks.Open("c:\Data\AAA.csv")
Set rng = Range("A1").CurrentRegion.Columns(1).Cells
For Each cell In rng
s = cell.Text
y = Left(s, 4)
m = Mid(s, 5, 2)
d = Mid(s, 7, 2)
dt = CDate(m & "/" & d & "/" & y)
cell.Value = dt
cell.NumberFormat = "mm/dd/yyyy"
For i = 1 To 4
cell.Offset(0, i).Value = cell.Offset(0, i).Value * 100
Next
Next
Columns(2).Insert
rng.Offset(0, 1).Value = 1
For i = 3 To 5
Set rng1 = Cells(Rows.Count, 1).End(xlUp)(2)
rng.Copy Destination:=rng1
rng.Offset(0, i).Copy Destination:=rng1.Offset(0, 2)
rng1.Offset(0, 1).Resize(rng.Count).Value = i - 1
Next
Columns("D:F").Delete
Columns("B").NumberFormat = "General"
End Sub

--
Regards,
Tom Ogilvy



"Chris" wrote:

I would like to create an application using MS Excel. Here is the tast at hand
I have a .csv file I need to import once that file is imported I need to
change one of the column so that the date would be mmddyy

Sample data of the .csv

Date 1 2 3 4
20051231 0.0446 0.0455 0.0461 0.0466
20051230 0.0445 0.0454 0.046 0.0465
20051229 0.0445 0.0453 0.0459 0.0465
20051228 0.0445 0.0453 0.0459 0.0464
20051227 0.0444 0.0453 0.0458 0.0464
20051226 0.0444 0.0453 0.0458 0.0464
20051225 0.0444 0.0453 0.0458 0.0464
20051224 0.0444 0.0453 0.0458 0.0464
20051223 0.0444 0.0453 0.0458 0.0464

Result I am looking to get

Date Term Rate
12/31/2005 1 4.46
12/30/2005 1 4.45
12/29/2005 1 4.45
12/28/2005 1 4.45
12/27/2005 1 4.44
12/26/2005 1 4.44
12/25/2005 1 4.44
12/24/2005 1 4.44
12/23/2005 1 4.44
12/31/2005 2 4.55
12/30/2005 2 4.54
12/29/2005 2 4.53
12/28/2005 2 4.53
12/27/2005 2 4.53
12/26/2005 2 4.53
12/25/2005 2 4.53
12/24/2005 2 4.53
12/23/2005 2 4.53
12/31/2005 3 4.61
12/30/2005 3 4.6
12/29/2005 3 4.59
12/28/2005 3 4.59
12/27/2005 3 4.58
12/26/2005 3 4.58
12/25/2005 3 4.58
12/24/2005 3 4.58
12/23/2005 3 4.58
12/31/2005 4 4.66
12/30/2005 4 4.65
12/29/2005 4 4.65
12/28/2005 4 4.64
12/27/2005 4 4.64
12/26/2005 4 4.64
12/25/2005 4 4.64
12/24/2005 4 4.64
12/23/2005 4 4.64

Reply With QuoteReply With Quote
  #3   Report Post  
Posted to microsoft.public.excel.programming
external usenet poster
  
Posts: 788
Default Aplication to import and Convert Data
Thanks

I am getting the following error message when I tried your code
Complie Error

Expected End Sub

"Tom Ogilvy" wrote:

You said a CSV file, so if your file actually looks like this:

20051231,0.0446,0.0455,0.0461,0.0466
20051230,0.0445,0.0454,0.0460,0.0465
20051229,0.0445,0.0453,0.0459,0.0465
20051228,0.0445,0.0453,0.0459,0.0464
20051227,0.0444,0.0453,0.0458,0.0464
20051226,0.0444,0.0453,0.0458,0.0464
20051225,0.0444,0.0453,0.0458,0.0464
20051224,0.0444,0.0453,0.0458,0.0464
20051223,0.0444,0.0453,0.0458,0.0464

then this will work:

Sub aBC()
Dim bk As Workbook, rng As Range
Dim s As String, y As String, m As String
Dim d As String, dt As Date, rng1 As Range
Set bk = Workbooks.Open("c:\Data\AAA.csv")
Set rng = Range("A1").CurrentRegion.Columns(1).Cells
For Each cell In rng
s = cell.Text
y = Left(s, 4)
m = Mid(s, 5, 2)
d = Mid(s, 7, 2)
dt = CDate(m & "/" & d & "/" & y)
cell.Value = dt
cell.NumberFormat = "mm/dd/yyyy"
For i = 1 To 4
cell.Offset(0, i).Value = cell.Offset(0, i).Value * 100
Next
Next
Columns(2).Insert
rng.Offset(0, 1).Value = 1
For i = 3 To 5
Set rng1 = Cells(Rows.Count, 1).End(xlUp)(2)
rng.Copy Destination:=rng1
rng.Offset(0, i).Copy Destination:=rng1.Offset(0, 2)
rng1.Offset(0, 1).Resize(rng.Count).Value = i - 1
Next
Columns("D:F").Delete
Columns("B").NumberFormat = "General"
End Sub

--
Regards,
Tom Ogilvy



"Chris" wrote:

I would like to create an application using MS Excel. Here is the tast at hand
I have a .csv file I need to import once that file is imported I need to
change one of the column so that the date would be mmddyy

Sample data of the .csv

Date 1 2 3 4
20051231 0.0446 0.0455 0.0461 0.0466
20051230 0.0445 0.0454 0.046 0.0465
20051229 0.0445 0.0453 0.0459 0.0465
20051228 0.0445 0.0453 0.0459 0.0464
20051227 0.0444 0.0453 0.0458 0.0464
20051226 0.0444 0.0453 0.0458 0.0464
20051225 0.0444 0.0453 0.0458 0.0464
20051224 0.0444 0.0453 0.0458 0.0464
20051223 0.0444 0.0453 0.0458 0.0464

Result I am looking to get

Date Term Rate
12/31/2005 1 4.46
12/30/2005 1 4.45
12/29/2005 1 4.45
12/28/2005 1 4.45
12/27/2005 1 4.44
12/26/2005 1 4.44
12/25/2005 1 4.44
12/24/2005 1 4.44
12/23/2005 1 4.44
12/31/2005 2 4.55
12/30/2005 2 4.54
12/29/2005 2 4.53
12/28/2005 2 4.53
12/27/2005 2 4.53
12/26/2005 2 4.53
12/25/2005 2 4.53
12/24/2005 2 4.53
12/23/2005 2 4.53
12/31/2005 3 4.61
12/30/2005 3 4.6
12/29/2005 3 4.59
12/28/2005 3 4.59
12/27/2005 3 4.58
12/26/2005 3 4.58
12/25/2005 3 4.58
12/24/2005 3 4.58
12/23/2005 3 4.58
12/31/2005 4 4.66
12/30/2005 4 4.65
12/29/2005 4 4.65
12/28/2005 4 4.64
12/27/2005 4 4.64
12/26/2005 4 4.64
12/25/2005 4 4.64
12/24/2005 4 4.64
12/23/2005 4 4.64

Reply With QuoteReply With Quote
  #4   Report Post  
Posted to microsoft.public.excel.programming
external usenet poster
  
Posts: 6,953
Default Aplication to import and Convert Data
I would guess that you didn't copy all the code since it does include an End
Sub statement in what I posted. Also, I tested it and the code was copied
directly from the module where it was working exactly as expected.

--
Regards,
Tom Ogilvy


"Chris" wrote:

Thanks

I am getting the following error message when I tried your code
Complie Error

Expected End Sub

"Tom Ogilvy" wrote:

You said a CSV file, so if your file actually looks like this:

20051231,0.0446,0.0455,0.0461,0.0466
20051230,0.0445,0.0454,0.0460,0.0465
20051229,0.0445,0.0453,0.0459,0.0465
20051228,0.0445,0.0453,0.0459,0.0464
20051227,0.0444,0.0453,0.0458,0.0464
20051226,0.0444,0.0453,0.0458,0.0464
20051225,0.0444,0.0453,0.0458,0.0464
20051224,0.0444,0.0453,0.0458,0.0464
20051223,0.0444,0.0453,0.0458,0.0464

then this will work:

Sub aBC()
Dim bk As Workbook, rng As Range
Dim s As String, y As String, m As String
Dim d As String, dt As Date, rng1 As Range
Set bk = Workbooks.Open("c:\Data\AAA.csv")
Set rng = Range("A1").CurrentRegion.Columns(1).Cells
For Each cell In rng
s = cell.Text
y = Left(s, 4)
m = Mid(s, 5, 2)
d = Mid(s, 7, 2)
dt = CDate(m & "/" & d & "/" & y)
cell.Value = dt
cell.NumberFormat = "mm/dd/yyyy"
For i = 1 To 4
cell.Offset(0, i).Value = cell.Offset(0, i).Value * 100
Next
Next
Columns(2).Insert
rng.Offset(0, 1).Value = 1
For i = 3 To 5
Set rng1 = Cells(Rows.Count, 1).End(xlUp)(2)
rng.Copy Destination:=rng1
rng.Offset(0, i).Copy Destination:=rng1.Offset(0, 2)
rng1.Offset(0, 1).Resize(rng.Count).Value = i - 1
Next
Columns("D:F").Delete
Columns("B").NumberFormat = "General"
End Sub

--
Regards,
Tom Ogilvy



"Chris" wrote:

I would like to create an application using MS Excel. Here is the tast at hand
I have a .csv file I need to import once that file is imported I need to
change one of the column so that the date would be mmddyy

Sample data of the .csv

Date 1 2 3 4
20051231 0.0446 0.0455 0.0461 0.0466
20051230 0.0445 0.0454 0.046 0.0465
20051229 0.0445 0.0453 0.0459 0.0465
20051228 0.0445 0.0453 0.0459 0.0464
20051227 0.0444 0.0453 0.0458 0.0464
20051226 0.0444 0.0453 0.0458 0.0464
20051225 0.0444 0.0453 0.0458 0.0464
20051224 0.0444 0.0453 0.0458 0.0464
20051223 0.0444 0.0453 0.0458 0.0464

Result I am looking to get

Date Term Rate
12/31/2005 1 4.46
12/30/2005 1 4.45
12/29/2005 1 4.45
12/28/2005 1 4.45
12/27/2005 1 4.44
12/26/2005 1 4.44
12/25/2005 1 4.44
12/24/2005 1 4.44
12/23/2005 1 4.44
12/31/2005 2 4.55
12/30/2005 2 4.54
12/29/2005 2 4.53
12/28/2005 2 4.53
12/27/2005 2 4.53
12/26/2005 2 4.53
12/25/2005 2 4.53
12/24/2005 2 4.53
12/23/2005 2 4.53
12/31/2005 3 4.61
12/30/2005 3 4.6
12/29/2005 3 4.59
12/28/2005 3 4.59
12/27/2005 3 4.58
12/26/2005 3 4.58
12/25/2005 3 4.58
12/24/2005 3 4.58
12/23/2005 3 4.58
12/31/2005 4 4.66
12/30/2005 4 4.65
12/29/2005 4 4.65
12/28/2005 4 4.64
12/27/2005 4 4.64
12/26/2005 4 4.64
12/25/2005 4 4.64
12/24/2005 4 4.64
12/23/2005 4 4.64

Reply With QuoteReply With Quote
  #5   Report Post  
Posted to microsoft.public.excel.programming
external usenet poster
  
Posts: 788
Default Aplication to import and Convert Data
Tom,

I am getting another error (a debug error)

the following line is highlighted

" dt = CDate(m & "/" & d & "/" & y)"

Can I send you the file I have and maybe you can fixed it for us.

Please I really need your help
"Tom Ogilvy" wrote:

I would guess that you didn't copy all the code since it does include an End
Sub statement in what I posted. Also, I tested it and the code was copied
directly from the module where it was working exactly as expected.

--
Regards,
Tom Ogilvy


"Chris" wrote:

Thanks

I am getting the following error message when I tried your code
Complie Error

Expected End Sub

"Tom Ogilvy" wrote:

You said a CSV file, so if your file actually looks like this:

20051231,0.0446,0.0455,0.0461,0.0466
20051230,0.0445,0.0454,0.0460,0.0465
20051229,0.0445,0.0453,0.0459,0.0465
20051228,0.0445,0.0453,0.0459,0.0464
20051227,0.0444,0.0453,0.0458,0.0464
20051226,0.0444,0.0453,0.0458,0.0464
20051225,0.0444,0.0453,0.0458,0.0464
20051224,0.0444,0.0453,0.0458,0.0464
20051223,0.0444,0.0453,0.0458,0.0464

then this will work:

Sub aBC()
Dim bk As Workbook, rng As Range
Dim s As String, y As String, m As String
Dim d As String, dt As Date, rng1 As Range
Set bk = Workbooks.Open("c:\Data\AAA.csv")
Set rng = Range("A1").CurrentRegion.Columns(1).Cells
For Each cell In rng
s = cell.Text
y = Left(s, 4)
m = Mid(s, 5, 2)
d = Mid(s, 7, 2)
dt = CDate(m & "/" & d & "/" & y)
cell.Value = dt
cell.NumberFormat = "mm/dd/yyyy"
For i = 1 To 4
cell.Offset(0, i).Value = cell.Offset(0, i).Value * 100
Next
Next
Columns(2).Insert
rng.Offset(0, 1).Value = 1
For i = 3 To 5
Set rng1 = Cells(Rows.Count, 1).End(xlUp)(2)
rng.Copy Destination:=rng1
rng.Offset(0, i).Copy Destination:=rng1.Offset(0, 2)
rng1.Offset(0, 1).Resize(rng.Count).Value = i - 1
Next
Columns("D:F").Delete
Columns("B").NumberFormat = "General"
End Sub

--
Regards,
Tom Ogilvy



"Chris" wrote:

I would like to create an application using MS Excel. Here is the tast at hand
I have a .csv file I need to import once that file is imported I need to
change one of the column so that the date would be mmddyy

Sample data of the .csv

Date 1 2 3 4
20051231 0.0446 0.0455 0.0461 0.0466
20051230 0.0445 0.0454 0.046 0.0465
20051229 0.0445 0.0453 0.0459 0.0465
20051228 0.0445 0.0453 0.0459 0.0464
20051227 0.0444 0.0453 0.0458 0.0464
20051226 0.0444 0.0453 0.0458 0.0464
20051225 0.0444 0.0453 0.0458 0.0464
20051224 0.0444 0.0453 0.0458 0.0464
20051223 0.0444 0.0453 0.0458 0.0464

Result I am looking to get

Date Term Rate
12/31/2005 1 4.46
12/30/2005 1 4.45
12/29/2005 1 4.45
12/28/2005 1 4.45
12/27/2005 1 4.44
12/26/2005 1 4.44
12/25/2005 1 4.44
12/24/2005 1 4.44
12/23/2005 1 4.44
12/31/2005 2 4.55
12/30/2005 2 4.54
12/29/2005 2 4.53
12/28/2005 2 4.53
12/27/2005 2 4.53
12/26/2005 2 4.53
12/25/2005 2 4.53
12/24/2005 2 4.53
12/23/2005 2 4.53
12/31/2005 3 4.61
12/30/2005 3 4.6
12/29/2005 3 4.59
12/28/2005 3 4.59
12/27/2005 3 4.58
12/26/2005 3 4.58
12/25/2005 3 4.58
12/24/2005 3 4.58
12/23/2005 3 4.58
12/31/2005 4 4.66
12/30/2005 4 4.65
12/29/2005 4 4.65
12/28/2005 4 4.64
12/27/2005 4 4.64
12/26/2005 4 4.64
12/25/2005 4 4.64
12/24/2005 4 4.64
12/23/2005 4 4.64

Reply With QuoteReply With Quote
  #6   Report Post  
Posted to microsoft.public.excel.programming
external usenet poster
  
Posts: 27,285
Default Aplication to import and Convert Data
Chris, you can send it. Make sure you include an email I can send it back
to.


--
Regards,
Tom Ogilvy

"Chris" wrote in message
...
Tom,

I am getting another error (a debug error)

the following line is highlighted

" dt = CDate(m & "/" & d & "/" & y)"

Can I send you the file I have and maybe you can fixed it for us.

Please I really need your help
"Tom Ogilvy" wrote:

I would guess that you didn't copy all the code since it does include an
End
Sub statement in what I posted. Also, I tested it and the code was
copied
directly from the module where it was working exactly as expected.

--
Regards,
Tom Ogilvy


"Chris" wrote:

Thanks

I am getting the following error message when I tried your code
Complie Error

Expected End Sub

"Tom Ogilvy" wrote:

You said a CSV file, so if your file actually looks like this:

20051231,0.0446,0.0455,0.0461,0.0466
20051230,0.0445,0.0454,0.0460,0.0465
20051229,0.0445,0.0453,0.0459,0.0465
20051228,0.0445,0.0453,0.0459,0.0464
20051227,0.0444,0.0453,0.0458,0.0464
20051226,0.0444,0.0453,0.0458,0.0464
20051225,0.0444,0.0453,0.0458,0.0464
20051224,0.0444,0.0453,0.0458,0.0464
20051223,0.0444,0.0453,0.0458,0.0464

then this will work:

Sub aBC()
Dim bk As Workbook, rng As Range
Dim s As String, y As String, m As String
Dim d As String, dt As Date, rng1 As Range
Set bk = Workbooks.Open("c:\Data\AAA.csv")
Set rng = Range("A1").CurrentRegion.Columns(1).Cells
For Each cell In rng
s = cell.Text
y = Left(s, 4)
m = Mid(s, 5, 2)
d = Mid(s, 7, 2)
dt = CDate(m & "/" & d & "/" & y)
cell.Value = dt
cell.NumberFormat = "mm/dd/yyyy"
For i = 1 To 4
cell.Offset(0, i).Value = cell.Offset(0, i).Value * 100
Next
Next
Columns(2).Insert
rng.Offset(0, 1).Value = 1
For i = 3 To 5
Set rng1 = Cells(Rows.Count, 1).End(xlUp)(2)
rng.Copy Destination:=rng1
rng.Offset(0, i).Copy Destination:=rng1.Offset(0, 2)
rng1.Offset(0, 1).Resize(rng.Count).Value = i - 1
Next
Columns("D:F").Delete
Columns("B").NumberFormat = "General"
End Sub

--
Regards,
Tom Ogilvy



"Chris" wrote:

I would like to create an application using MS Excel. Here is the
tast at hand
I have a .csv file I need to import once that file is imported I
need to
change one of the column so that the date would be mmddyy

Sample data of the .csv

Date 1 2 3 4
20051231 0.0446 0.0455 0.0461 0.0466
20051230 0.0445 0.0454 0.046 0.0465
20051229 0.0445 0.0453 0.0459 0.0465
20051228 0.0445 0.0453 0.0459 0.0464
20051227 0.0444 0.0453 0.0458 0.0464
20051226 0.0444 0.0453 0.0458 0.0464
20051225 0.0444 0.0453 0.0458 0.0464
20051224 0.0444 0.0453 0.0458 0.0464
20051223 0.0444 0.0453 0.0458 0.0464

Result I am looking to get

Date Term Rate
12/31/2005 1 4.46
12/30/2005 1 4.45
12/29/2005 1 4.45
12/28/2005 1 4.45
12/27/2005 1 4.44
12/26/2005 1 4.44
12/25/2005 1 4.44
12/24/2005 1 4.44
12/23/2005 1 4.44
12/31/2005 2 4.55
12/30/2005 2 4.54
12/29/2005 2 4.53
12/28/2005 2 4.53
12/27/2005 2 4.53
12/26/2005 2 4.53
12/25/2005 2 4.53
12/24/2005 2 4.53
12/23/2005 2 4.53
12/31/2005 3 4.61
12/30/2005 3 4.6
12/29/2005 3 4.59
12/28/2005 3 4.59
12/27/2005 3 4.58
12/26/2005 3 4.58
12/25/2005 3 4.58
12/24/2005 3 4.58
12/23/2005 3 4.58
12/31/2005 4 4.66
12/30/2005 4 4.65
12/29/2005 4 4.65
12/28/2005 4 4.64
12/27/2005 4 4.64
12/26/2005 4 4.64
12/25/2005 4 4.64
12/24/2005 4 4.64
12/23/2005 4 4.64



Reply With QuoteReply With Quote
Reply
Thread Tools Search this Thread
Search this Thread:

Advanced Search
Display Modes
Hybrid Mode Hybrid Mode

Posting Rules
Smilies are On
[IMG] code is On
HTML code is Off
Trackbacks are On
Pingbacks are On
Refbacks are On

Similar Threads
Thread Thread Starter Forum Replies Last Post
How to Start Excel in Text Import Wizard for data import rlelvis Setting up and Configuration of Excel 0 July 10th 08 08:40 PM
Convert/import into Excel JackovSpades Excel Discussion (Misc queries) 0 February 6th 06 09:33 PM
Import csv data and convert to TIME format JLettington Excel Worksheet Functions 0 January 25th 06 03:34 PM
Reading excel data into another aplication Sean Bartleet Excel Programming 2 October 19th 05 02:15 PM
How do I open an aplication and run a file in that aplication use. Cozy Excel Programming 1 March 28th 05 10:41 AM


All times are GMT +1. The time now is 11:49 PM.

Powered by vBulletin® Copyright ©2000 - 2025, Jelsoft Enterprises Ltd.
Copyright ©2004-2025 ExcelBanter.
The comments are property of their posters.
 

About Us

"It's about Microsoft Excel"

 

Contact Us ExcelBanter forum home Privacy Statement