如何将每个单独的excel行导出到其自己的单独的csv文件? [英] How to export each individual excel row to its own individual csv file?
问题描述
我正在尝试在Adobe Illustrator中构建可变数据报告.此报告将在excel/csv文件中列出每个人的图表和数据.为了能够生成图,需要有一个.csv文件,该文件的数据仅与与该特定个人相关的图有关.
I am trying to build a variable data report in Adobe Illustrator. This report will have graphs and data for each individual listed in an excel/csv file. To be able to generate the graphs there needs to be a .csv file of the data only pertaining to the graph relevant to that specific individual.
例如,我有三个人:约翰,乔和安.
So for example I have three individuals: John, Joe, and Ann.
每个人都将有一个条形图,显示他们在交流中的成长,并且还将有一个图,显示其在信心中的成长.
Each individual will have a bar graph showing their growth in communication, and they will also have a graph showing their growth in confidence.
每个人的两个图都需要一个csv.这意味着我总共需要6个csv文件才能开始在excel中构建变量数据报告.
Each individual needs a csv for both graphs. Which means I will need a total of 6 csv files to start building the variable data report in excel.
显然,所有数据都将集中在一个excel文件中,我需要将其分离出来并另存为单独的csv文件.
Obviously all of the data will be together in one excel file that I will need to separate out and save as the individual csv files.
是否可以使用一个脚本将每行(代表单个行)导出为单独的csv文件,并根据单元格A2和B1中的数据进行命名?
Is there a script I can use that will export each row (representative of the individual) as it's own separate csv file and name it based on the data in cell A2 and B1?
我进行了一些研究,发现一些解决方案仅引用列或如何导出选定的行,但是我将需要对100多个个人执行此操作,而这些解决方案将不适用于我需要它们的数量.我对VBA编码有点熟悉,但是我很难找到任何可以解决此问题的方法.
I have done some research and I have found some solutions that only reference the columns or how to export selected rows, but I will need to do this for 100+ individuals and these solutions will not work for the volume I need them to. I am somewhat familiar with VBA coding, but I am having a hard time finding anything that can help with this issue.
所以,如果我的细胞看起来像这样:
So if my cells look like this:
A B C
1 Name Communication Communication End
2 John 20 25
3 Joe 16 18
4 Ann 23 27
我的文件名将如下所示:John_Communication.csv,Joe_Communication.csv,Ann_Communication.csv
My file names will look like this: John_Communication.csv, Joe_Communication.csv, Ann_Communication.csv
并且仅将每行的数据作为自己的csv文件导出,以便我可以将数据导入Adobe Illustrator来创建个人报告所需的图形吗?
And will only export the data from each individual row as it's own csv file so that I can import the data into Adobe Illustrator to create the graphs I need for the individual's reports?
推荐答案
一种解决方法是从VBA宏中写出csv文件:
One way to approach this is by writing out csv files from VBA macro:
Sub SaveRowsToSeparateCsv()
Dim h As Range ' header
Dim d As Range ' data
Dim r As Range ' row
Dim c As Range ' cell
Dim i As Long
Dim ha2d As Variant ' header 2d array
Dim ha1d As Variant ' header 1d array
Dim hcsv As String ' header in csv format
Dim da2d As Variant ' data 2d array
Dim da1d As Variant ' data 1d array
Dim dcsv As String ' data in csv format
' init ranges with header and data
With [a1].CurrentRegion
Set h = .Rows(1)
Set d = .Offset(1, 0).Resize(.Rows.Count - 1, .Columns.Count)
End With
' we'll need header multiple times, so lets prepare it
ha2d = h.Value
ReDim ha1d(LBound(ha2d, 2) To UBound(ha2d, 2))
For i = LBound(ha1d) To UBound(ha1d)
ha1d(i) = ha2d(1, i)
Next
hcsv = Join(ha1d, ",") & vbNewLine
' now lets go through each row and save it seaprately as a csv
For Each r In d.Rows
da2d = r.Value
ReDim da1d(LBound(da2d, 2) To UBound(da2d, 2))
For i = LBound(da1d) To UBound(da1d)
da1d(i) = da2d(1, i)
Next
dcsv = hcsv & Join(da1d, ",") & vbNewLine
saveFile ThisWorkbook.Path & Application.PathSeparator _
& r.Cells(1) & "_Communication.csv", dcsv
Next r
End Sub
Sub saveFile(pathname As String, sText As String)
Dim fNum As Integer: fNum = FreeFile
Open pathname For Output As fNum
Print #fNum, sText;
Close #fNum
End Sub
这篇关于如何将每个单独的excel行导出到其自己的单独的csv文件?的文章就介绍到这了,希望我们推荐的答案对大家有所帮助,也希望大家多多支持IT屋!