Solved

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

Posted on 2013-01-18
10
182 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
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 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
Creating Instructional Tutorials  

For Any Use & On Any Platform

Contextual Guidance at the moment of need helps your employees/users adopt software o& achieve even the most complex tasks instantly. Boost knowledge retention, software adoption & employee engagement with easy solution.

 
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

The Eight Noble Truths of Backup and Recovery

How can IT departments tackle the challenges of a Big Data world? This white paper provides a roadmap to success and helps companies ensure that all their data is safe and secure, no matter if it resides on-premise with physical or virtual machines or in the cloud.

Question has a verified solution.

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

Preparing an email is something we should all take special care with – especially when the email is for somebody you may not know very well. The pressures of everyday working life stacked with a hectic office environment can make this a real challen…
It’s the first day of March, the weather is starting to warm up and the excitement of the upcoming St. Patrick’s Day holiday can be felt throughout the world.
This Micro Tutorial demonstrates how to create Excel charts: column, area, line, bar, and scatter charts. Formatting tips are provided as well.
This Micro Tutorial will demonstrate how to use a scrolling table in Microsoft Excel using the INDEX function.

751 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