Solved

Exce help needed - How do I  convert to a database loadable csv file

Posted on 2013-01-18
10
162 Views
Last Modified: 2013-02-27
I believe there must be an easier way to format the data in an excel spreadsheet
Can someone please help me out here???
Here is the problem.
The data looks as follows

StoreId,product,        price  StoreId,product,        price
1           ,Castle Toy,    30.00  2           PinePen        12.50
1            SeaPlane,     45.00   2           GumDrop     5.77
1            HoolaHup6, 12.67   2           UniveralFan 26.99   ...

The data is broken out across the columns by storeID e.g 3,4,5,6... N
Right now I copy and paste each 2 columns in an effort to create a 2 field list for loading
How would I do this in a more efficient manner in  Excel???
StoreId,product,        price
1           ,Castle Toy,    30.00
1            SeaPlane,     45.00  
1            HoolaHup6, 12.67
2           PinePen        12.50
2           GumDrop     5.77
2           UniveralFan 26.99


I imagine for the skilled Excel person this is simple - Please advise!!
0
Comment
Question by:Robert Silver
  • 4
  • 3
  • 2
  • +1
10 Comments
 
LVL 120

Expert Comment

by:Rey Obrero (Capricorn1)
ID: 38795121
are the column names in excel exactly like this

StoreId,product,        price  StoreId,product,        price
0
 
LVL 92

Expert Comment

by:Patrick Matthews
ID: 38795164
A sample file would be helpful...
0
 
LVL 120

Expert Comment

by:Rey Obrero (Capricorn1)
ID: 38795175
if the columns is what you posted,
change column names like this

StoreId,product,        price  StoreId1 ,product1,        price1

then, from access db, create a link to the excel file..

you can now combine the columns with a union query


select StoreId,product, price from tableExcel
union all

select StoreId1,product1, price1 from tableExcel
0
Complete VMware vSphere® ESX(i) & Hyper-V Backup

Capture your entire system, including the host, with patented disk imaging integrated with VMware VADP / Microsoft VSS and RCT. RTOs is as low as 15 seconds with Acronis Active Restore™. You can enjoy unlimited P2V/V2V migrations from any source (even from a different hypervisor)

 
LVL 26

Accepted Solution

by:
redmondb earned 500 total points
ID: 38795369
Hi, rssdds.

I couldn't decode how the input data was organised, so in the attached I've taken a chance and assumed that there shouldn't be a space in ",Castle Toy,". (Although, now that I look at it again, is it possible that a comma before a string is a text qualifier?)

Please change the text file's path and name as necessary.
Option Explicit

Sub Split_Stores()
Dim i          As Long
Dim j          As Long
Dim xLast_Col  As Long
Dim xLast_Row  As Long
Dim xLast_Dest As Long
Dim xLast_Item As Long
Dim xSrce      As Worksheet
Dim xDest      As Worksheet

Application.ScreenUpdating = False
    
    Workbooks.OpenText Filename:="D:\Store_Details.txt", _
            Origin:=xlMSDOS, StartRow:=1, DataType:=xlDelimited, TextQualifier:= _
            xlDoubleQuote, ConsecutiveDelimiter:=True, Tab:=False, Semicolon:=False, _
            Comma:=False, Space:=True, Other:=False, FieldInfo:=Array(Array(1, 1)), _
            TrailingMinusNumbers:=True
    Set xSrce = ActiveWorkbook.Sheets(1)
    
    If xSrce.UsedRange.Rows.Count < 1 Then Debug.Print "!?"
    xLast_Row = xSrce.Range("A1").SpecialCells(xlLastCell).Row
    xLast_Col = xSrce.Range("A1").SpecialCells(xlLastCell).Column
    
    If xLast_Row < 2 Or xLast_Col Mod 3 <> 0 Then
        xSrce.Activate
        MsgBox ("Invalid rows (" & xLast_Row & ") or columns (" & xLast_Col & ") - run cancelled.")
        Exit Sub
    End If
    
    Set xDest = ThisWorkbook.Worksheets.Add
    xDest.Range("A1:C1") = Array("Store ID", "Product", "Price")
    
    For i = 1 To (xLast_Col - 2) Step 3
        xLast_Item = xSrce.Cells(Rows.Count, i).End(xlUp).Row
        If xLast_Item > 1 Then
            xLast_Dest = xDest.Range("A1").SpecialCells(xlLastCell).Row
            xSrce.Range(xSrce.Cells(2, i), xSrce.Cells(xLast_Item, i + 2)).Copy Destination:=xDest.Range("A" & xLast_Dest + 1)
        Else
            MsgBox ("No data from column no. " & i & " - missing store bypassed.")
        End If
    Next
    
    xDest.Columns("B:B").Replace What:=",", Replacement:="", LookAt:=xlPart, _
            SearchOrder:=xlByColumns, MatchCase:=False, SearchFormat:=False, _
            ReplaceFormat:=False
    
    xSrce.Parent.Close savechanges:=False
    
    xDest.Activate
    
Application.ScreenUpdating = True

End Sub

Open in new window

Regards,
Brian.
Split-Stores-V2.xlsm
Store-Details.txt
0
 
LVL 2

Author Comment

by:Robert Silver
ID: 38795968
I can program - I am looking for a quick Excel is solution like maybe a menu  method or such...  It seems to me there should be a way to facilitate it

I will get back to you in a week
0
 
LVL 26

Expert Comment

by:redmondb
ID: 38796369
rssdds,

Pasting data directly into a question can cause layout problems, so please post a larger sample as a text file. This may give us a clearer view of the file's format.

Also, do you want the commas in the output list?

Brian.
0
 
LVL 2

Author Comment

by:Robert Silver
ID: 38935755
I finally got to this.  Brian what References does this code require on an Access 2010
to run or do I need to run it in Excel? If so how? I am only aware of running it in Access.
Not a lot of error messages display to tell me which libraries I need

As soon as I run this in Access 2010 successfully you get A+ points but now I struggle to know what references the subroutine needs. I even tried to change it into a function that returns a variant and running in in the immediate window: ? split_Stores()
So far I have :
     Visual Basic For Applications
     Microsoft Access 14.0 Object library
     OLE Automation
     Microsoft Office 14.0 Access database engine Object Library
   
The compile shows Worksheet is a user defined type not defined after compiling....
0
 
LVL 26

Expert Comment

by:redmondb
ID: 38936032
rssdds,

As soon as I run this in Access 2010 successfully...
You'd repeatedly mentioned Excel assistance in an Excel Topic so I provided an Excel solution. At this point, let's continue with that and check that the logic's correct. Once that's done, we can convert it to Access.

...you get A+ points
Only "A" and only when you've got the solution you need.

do I need to run it in Excel? If so how?
(1) Open Split-Stores-V2.xlsm.
(2) Edit its Split_Stores() macro to reflect your file's name and path.
(3) Click the blue button on Sheet1.

Brian.
0
 
LVL 2

Author Closing Comment

by:Robert Silver
ID: 38936177
Note this module must be tailored a bit but it seems to work what it does is create a worksheet output as a list of data from the horizontal positions of repeating data down and across

No need to Access -ize it but note Microsoft does not make this easy at all especially if you worked on versions of Office 2003 and backwards.
This must be loaded into a module and run within Excel!
0
 
LVL 26

Expert Comment

by:redmondb
ID: 38936248
rssdds,

This must be loaded into a module and run within Excel!
Nope - it's already there!

what it does is create a worksheet output as a list of data from the horizontal positions of repeating data down and across
Other than some uncertainty around the delimiter, it seems to produce exactly the sample output in your question

Note this module must be tailored a bit
Other than the text file's path and name that I mentioned, is there anything else that needs changing?

Brian.
0

Featured Post

Simplifying Server Workload Migrations

This use case outlines the migration challenges that organizations face and how the Acronis AnyData Engine supports physical-to-physical (P2P), physical-to-virtual (P2V), virtual to physical (V2P), and cross-virtual (V2V) migration scenarios to address these challenges.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

In a multiple monitor setup, if you don't want to use AutoCenter to position your popup forms, you have a problem: where will they appear?  Sometimes you may have an additional problem: where the devil did they go?  If you last had a popup form open…
I see at least one EE question a week that pertains to using temporary tables in MS Access.  But surprisingly, I was unable to find a single article devoted solely to this topic. I don’t intend to describe all of the uses of temporary tables in t…
Graphs within dashboards are meant to be dynamic, representing data from a period of time that will change each time the dashboard is updated with new data. Rather than update each graph to point to a different set within a static set of data, t…
Many functions in Excel can make decisions. The most simple of these is the IF function: it returns a value depending on whether a condition you describe is true or false. Once you get the hang of using the IF function, you will find it easier to us…

785 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question