This is a migrated thread and some comments may be shown as answers.

First Row Not Being Exported to CSV

6 Answers 170 Views

Grid

This is a migrated thread and some comments may be shown as answers.

Geo asked on 01 Apr 2019, 10:29 PM

As the title describes I am having an issue exporting some data from a grid to CSV. Here is our use case:

We query data from our database, the page examines the data and then displays errors for each cell where the data is improper for the external system that the data will eventually be fed into. After the user fixes the "first stage" errors another two error checking stages are enabled. Once these stages are also passed, the user can then click on a button that hides all of the original rows and displays the rows we have added to the grid for export. At this point all of the data in the database for these records has been fixed.

Once they click on the Prepare Data for Export button, they will then be able to click on the Export Data button. Both of these buttons are asp buttons, we are not using the built-in export buttons.

So, once the export button is pressed the file is generated and all of our data that we want to be exported is however we have a problem where the first row in the grid is not being exported. In the export file, the first row is blank and the ID for the record does not show up in any other row.

Here is a scrubbed example:

https://www.dropbox.com/s/m562savvovi8fmp/GridExport_Scrubbed.csv?dl=0

Here is the grid definition:

<telerik:RadGrid ID="ClaimExportGrid" runat="server" AllowAutomaticUpdates="false" DataSourceID="DDDClaimDS" OnGridExporting="OnClaimExportGridExporting" AllowFilteringByColumn="true" AllowPaging="true" PageSize="50" VirtualItemCount="50" OnExcelExportCellFormatting="OnClaimExportGridExcelExportFormatting" OnItemCreated="OnClaimExportGridItemCreated" OnItemDataBound="OnClaimExportGridItemDataBound" OnUpdateCommand="OnClaimExportGridUpdateCommand" AutoGenerateColumns="false">

<ClientSettings EnableRowHoverStyle="true"/>

<ExportSettings ExportOnlyData="true" FileName="ClinicDDDExport" HideStructureColumns="True" IgnorePaging="true" OpenInNewWindow="true"/>

<MasterTableView EditMode="InPlace">

<Columns>

<telerik:GridCheckBoxColumn UniqueName="BlkEdtEnabled" AllowFiltering="false" Display="false" HeaderText="Bulk Edit"/>

<telerik:GridCheckBoxColumn UniqueName="ChgInExptEnabled" AllowFiltering="false" Display="false" HeaderText="Change In Export"/>

<telerik:GridEditCommandColumn UniqueName="EditCommandColumn" />

<telerik:GridBoundColumn DataField="LOC" UniqueName="LOC_Export" AllowFiltering="false" HeaderText="LOC" Display="false" />

<telerik:GridBoundColumn DataField="Contract" UniqueName="Contract_Export" AllowFiltering="false" HeaderText="Contract" Display="false" />

<telerik:GridBoundColumn DataField="Assist" HeaderText="Assist" Display="false" AllowFiltering="false" UniqueName="Assist_Export" />

<telerik:GridBoundColumn DataField="Start Date" HeaderText="Start Date" AllowFiltering="false" DataFormatString="{0:MM/dd/yyyy}" UniqueName="StartDate_Export" Display="false"/>

<telerik:GridBoundColumn DataField="End Date" HeaderText="End Date" AllowFiltering="false" DataFormatString="{0:MM/dd/yyyy}" UniqueName="EndDate_Export" Display="false" />

<telerik:GridBoundColumn DataField="CPT" HeaderText="CPT" AllowFiltering="true" Display="false" UniqueName="CPT_Export" />

<telerik:GridBoundColumn DataField="Nursing" HeaderText="Nursing" AllowFiltering="false" UniqueName="Nursing_Export" Display="false"/>

<telerik:GridBoundColumn DataField="Units Delivered" HeaderText="Delivered" AllowFiltering="false" DataFormatString="{0:F2}" UniqueName="UnitsDelivered_Export" Display="false"/>

<telerik:GridBoundColumn DataField="Units Absent" HeaderText="Absent" AllowFiltering="false" DataFormatString="{0:F2}" UniqueName="UnitsAbsent_Export" Display="false"/>

<telerik:GridBoundColumn DataField="ServiceRate" HeaderText="ServRate" AllowFiltering="false" DataFormatString="{0:F2}" UniqueName="ServiceRate_Export" Display="false"/>

<telerik:GridBoundColumn DataField="TPLID1" HeaderText="TPL1ID" AllowFiltering="false" UniqueName="TPLID1_Export" Display="false"/>

<telerik:GridBoundColumn DataField="TPLReceived1" HeaderText="TPL1Rec" AllowFiltering="false" DataFormatString="{0:F2}" UniqueName="TPLReceived1_Export" Display="false"/>

<telerik:GridBoundColumn DataField="TPLCode1" UniqueName="TPL1Code_Export" AllowFiltering="false" HeaderText="TPL1Code" Display="false" />

<telerik:GridBoundColumn DataField="TPLID2" HeaderText="TPL2ID" AllowFiltering="false" UniqueName="TPLID2_Export" Display="false"/>

<telerik:GridBoundColumn DataField="TPLReceived2" HeaderText="TPL2Rec" AllowFiltering="false" DataFormatString="{0:F2}" UniqueName="TPLReceived2_Export" Display="false"/>

<telerik:GridBoundColumn DataField="TPLCode2" UniqueName="TPL2Code_Export" AllowFiltering="false" HeaderText="TPL2Code" Display="false" />

<telerik:GridBoundColumn DataField="TPLID3" HeaderText="TPL3ID" AllowFiltering="false" UniqueName="TPLID3_Export" Display="false"/>

<telerik:GridBoundColumn DataField="TPLReceived3" HeaderText="TPL3Rec" AllowFiltering="false" DataFormatString="{0:F2}" UniqueName="TPLReceived3_Export" Display="false"/>

<telerik:GridBoundColumn DataField="TPLCode3" UniqueName="TPL3Code_Export" AllowFiltering="false" HeaderText="TPL3Code" Display="false" />

<telerik:GridBoundColumn DataField="DDDSubmit" HeaderText="DDDSubmit" AllowFiltering="false" DataFormatString="{0:F2}" UniqueName="DDDSubmit_Export" Display="false"/>

<telerik:GridBoundColumn DataField="ProvCtrl" HeaderText="ProvCtrl" AllowFiltering="false" UniqueName="ProvCtrl_Export" Display="false"/>

<telerik:GridBoundColumn DataField="AHCCCSID" HeaderText="AHCCCSID" AllowFiltering="false" UniqueName="AHCCCSID_Export" Display="false"/>

<telerik:GridBoundColumn DataField="ClaimID" HeaderText="ClaimID" AllowFiltering="false" UniqueName="ClaimID_Export" Display="false" />

<telerik:GridBoundColumn DataField="NPI" HeaderText="NPI" AllowFiltering="false" UniqueName="NPI_Export" Display="false"/>

<telerik:GridBoundColumn DataField="PlaceOfService" HeaderText="PlaceOfService" AllowFiltering="false" UniqueName="POS_Export" Display="false"/>

<telerik:GridBoundColumn DataField="SupName" HeaderText="SupervisorName" AllowFiltering="false" UniqueName="SupName_Export" Display="false"/>

<telerik:GridBoundColumn DataField="TherapistName" HeaderText="RenderTherapistName" AllowFiltering="false" UniqueName="TherapistName_Export" Display="false"/>

<telerik:GridBoundColumn DataField="PatientName" HeaderText="PatientName" AllowFiltering="false" UniqueName="PatientName_Export" Display="false"/>

<telerik:GridBoundColumn DataField="Tier" HeaderText="Tier" AllowFiltering="false" UniqueName="Tier_Export" Display="false" />

<telerik:GridBoundColumn DataField="ModifiedOn" HeaderText="ModifiedOn" AllowFiltering="false" UniqueName="ModifiedOn_Export" Display="false" />

<telerik:GridBoundColumn DataField="ModifiedBy" HeaderText="ModifiedBy" AllowFiltering="false" UniqueName="ModifiedBy_Export" Display="false" />

<telerik:GridBoundColumn DataField="ClaimID" HeaderText="ClaimID" AllowFiltering="false" UniqueName="ClaimID" />

<telerik:GridBoundColumn DataField="PatientID" HeaderText="PatientID" AllowFiltering="false" UniqueName="PatientID" />

<telerik:GridBoundColumn DataField="Assist" HeaderText="Assist" AllowFiltering="false" UniqueName="Assist" />

<telerik:GridBoundColumn DataField="Start Date" HeaderText="Start Date" AllowFiltering="false" DataFormatString="{0:MM/dd/yyyy}" UniqueName="StartDate" />

<telerik:GridBoundColumn DataField="End Date" HeaderText="End Date" AllowFiltering="false" DataFormatString="{0:MM/dd/yyyy}" UniqueName="EndDate" />

<telerik:GridBoundColumn DataField="CPT" HeaderText="CPT" AllowFiltering="false" UniqueName="CPT" />

<telerik:GridBoundColumn DataField="Units Delivered" AllowFiltering="false" HeaderText="Delivered" DataFormatString="{0:F2}" UniqueName="UnitsDelivered" />

<telerik:GridBoundColumn DataField="Units Absent" AllowFiltering="false" HeaderText="Absent" DataFormatString="{0:F2}" UniqueName="UnitsAbsent" />

<telerik:GridBoundColumn DataField="ServiceRate" AllowFiltering="false" HeaderText="Serv. Rate" DataFormatString="{0:F2}" UniqueName="ServiceRate" />

<telerik:GridBoundColumn DataField="TPLID1" AllowFiltering="false" HeaderText="TPL1 ID" UniqueName="TPLID1" />

<telerik:GridBoundColumn DataField="TPLReceived1" HeaderText="TPL1 Recv." AllowFiltering="false" DataFormatString="{0:F2}" UniqueName="TPLReceived1" />

<telerik:GridBoundColumn DataField="DDDSubmit" HeaderText="DDD Submit" AllowFiltering="false" DataFormatString="{0:F2}" UniqueName="DDDSubmit" />

<telerik:GridBoundColumn DataField="RenderingTherapistID" AllowFiltering="false" HeaderText="RenderingTherapistID" UniqueName="RenderingTherapistID" />

<telerik:GridBoundColumn DataField="EmployeeID" HeaderText="SupervisorID" AllowFiltering="false" UniqueName="EmployeeID" />

<telerik:GridBoundColumn DataField="SupNAme" HeaderText="Supervisor Name" AllowFiltering="false" UniqueName="SupName" />

<telerik:GridBoundColumn DataField="AHCCCSID" HeaderText="AHCCCS ID" AllowFiltering="false" UniqueName="AHCCCSID" />

<telerik:GridBoundColumn DataField="NPI" HeaderText="NPI" AllowFiltering="false" UniqueName="NPI" />

</Columns>

</MasterTableView>

</telerik:RadGrid>

Here are the sections of the code where we are dealing with the grid before export:

OnItemDataBound event:

If ViewState("BExported") Then

If Not bCallOnce Then

EnableExportCols()

bCallOnce = True

End If

If eItmMap.Count > 0 Then

Dim conStr As String = ConfigurationManager.ConnectionStrings("H2T2SQLDBCConnectionString").ConnectionString

Dim newNPI As String = ""

Dim newAhcccsID As String = ""

If eItmMap.ContainsKey(grdDataItm.Item("ClaimID").Text) Then

Using con As New SqlConnection(conStr)

Using cmd As New SqlCommand("Select @AHCSID = AHCCCSID, @NPI = NPI from TherapistCredentials where EmployeeID = @NewSupID", con)

cmd.Parameters.Add("@NewSupID", SqlDbType.Int).Value = eItmMap(grdDataItm.Item("ClaimID").Text).GetSupID()

cmd.Parameters.Add("@AHCSID", SqlDbType.VarChar, 10)

cmd.Parameters.Add("@NPI", SqlDbType.VarChar, 20)

cmd.Parameters("@AHCSID").Direction = ParameterDirection.Output

cmd.Parameters("@NPI").Direction = ParameterDirection.Output

con.Open()

cmd.ExecuteNonQuery()

con.Close()

newNPI = If(IsDBNull(cmd.Parameters("@NPI").Value), "", cmd.Parameters("@NPI").Value)

newAhcccsID = If(IsDBNull(cmd.Parameters("@AHCSID").Value), "", cmd.Parameters("@AHCSID").Value)

grdDataItm.Item("NPI_Export").Text = newNPI

grdDataItm.Item("AHCCCSID_Export").Text = newAhcccsID

End Using

End If

Else

......

EnableExportCols Function:

Private Sub EnableExportCols()

For Each col As GridColumn In ClaimExportGrid.MasterTableView.Columns

If TypeOf col Is GridBoundColumn Then

Dim bCol As GridBoundColumn = CType(col, GridBoundColumn)

If bCol.UniqueName.Contains("Export") Then

bCol.Visible = True

bCol.Display = True

Else

bCol.Visible = False

bCol.Display = False

End If

Next

End Sub

As you can see in the attached csv file, the first row is missing and the ID CL 733083 is not present in the file. How can we resolve this?

Thanks.

6 Answers, 1 is accepted

answered on 04 Apr 2019, 02:07 AM

Seeing as the forum seems pretty dead I tried figuring out a work around, which I did. I added some code to look for a grid cell that has a value of   which would only be blank if it were not being properly picked up by the OnItemDataBound and set the values of the export columns to the values of the non-export columns for that row.

Got it to work eventually but had to futz around with readding anything I did on the other rows from other functions for certain columns to work with the code I wrote.

Not very sleek but gets the job done.

answered on 04 Apr 2019, 08:29 PM

Hi Geo,

I have inspected the code you've shared with us but I cannot see the method that does the actual exporting. You have mentioned, that exporting is done using ASP buttons instead of the built-in ones. Could you please tell us more, how the export method is called?

Also, I am wondering if the export structure is built manually or letting grid built and export it?

One of the server-side code is showing/hiding certain columns while the other one is manually setting the text of certain cells. Whether or not the condition "If eItmMap.ContainsKey(grdDataItm.Item("ClaimID").Text) Then" is met, this would not have any impact on the export output as it only couple of cells, not the entire row. I think, the grid would produce the same results without these conditions, that makes me believe the issue we are looking for is coming from somewhere else.

Furthermore, I think the opening/closing an SQL connection and querying the database for every row in the grid is a little rough. Wouldn't be better if you query the database once and store the results in the memory which can be re-used if needed? If you could tell us a bit more on what you would like to achieve, we would gladly show you an optimal way to achieve that.

Kind regards,
Attila Antal
Progress Telerik

Get quickly onboarded and successful with your Telerik and/or Kendo UI products with the Virtual Classroom free technical training, available to all active customers. Learn More.

answered on 05 Apr 2019, 06:50 AM

Hi Attila,

Thanks for the reply. The method you are asking about is simply a button that calls a sub that calls ClaimExportGrid.MasterTableView.ExportToCSV() and then I have a sub called OnGridExporting that alters the CSV markup to prevent Excel from stripping out leading zeros.

Also in response to your question about the map check, this check is part of a value replacement system. In some cases we want to override the table data with values which we have populated into a map earlier on in the data clean up process as sometimes the external system we are sending data to throws out errors when certain users have too many records for a single day, it is part of a requirement on their end. This replacement system allows us to specify a claim id number in a map as the key and then create an instance of a class I created called ExportItem which contains properties that we want to use to get the data to override the values for the row which has that claim id in it.

It is not meant to change data on every row. As for the question on the constant re-querying of the database. It may be an issue going forward but for now we only anticipate at the extreme around 5 to 12 records per export from this page will go through this value replacement code so we found it to be an acceptable overhead cost for now.

All of the table structure is included in the grid definition on the aspx page, we are only changing the display state of the columns we want to show/export throughout the execution of the page and then also populating some of the cells with static values in some cases. It makes the grid definition a bit of a mess but it saves us having to do a lot in the code behind.

The way I fixed the first row issue was to just rebuild that first row's data by taking the original values we populated the grid with and inserting them into the export columns. Not pretty but it worked. There was also an issue where at some points some fields would end up having no data in the claimID export cell for some reason when going through the replacement code which was fixed by checking for a blank cell value and if it was then using the original claimID from the originally displayed grid column.

Could the page be a bit faster? Yep, but for now it is functional which is all we need at the moment.

answered on 05 Apr 2019, 05:00 PM

Hi Geo,

Thank you for the insights, I understand now.

Regarding the export issue, you have mentioned that the CSV output is being modified within the OnGridExporting event. I think that this is what we are looking for. The code you have shared with us so far, would not skip any rows.

Can you show us what is being done in the OnGridExporting event? Also, to understand whether the issue comes from the Grid or the custom code, the easiest way to test would be by removing all the Events that are wired up for the grid, and click on the export. If all the data is exported, then the problem will most likely be with one of the events where the output is getting modified.

Otherwise, if the issue is still present, then we must take a look at the grid settings.

Please try the suggestions from above and let us know how it goes.

Kind regards,
Attila Antal
Progress Telerik

Get quickly onboarded and successful with your Telerik and/or Kendo UI products with the Virtual Classroom free technical training, available to all active customers. Learn More.

answered on 05 Apr 2019, 09:42 PM

Hi Attila,

Here is the code which adds an = character to prevent Excel from removing the leading zeros, I also added a line of code to check for any   values (which are replaced with spaces in the output) as they were causing issues in fields that did not have any value in the external system:

Protected Sub OnClaimExportGridExporting(sender As Object, e As GridExportingArgs)

e.ExportOutput = e.ExportOutput.Replace("""0", "=""0") 'To keep leading zeros

e.ExportOutput = e.ExportOutput.Replace(""" """, """""") 'To remove from null telerik cells

End Sub

With this you have all of the functions that are modifying any data from the grid/exported data. All of the other events are either just setting the display property of columns to true or false or are adding styling to grid cells as part of the error detection system on the page. Everything else in the code deals with getting the changes from the table and inserting them into the database through some manual update functions and subs.

I do think that the original issue was/is being caused by a line of code somewhere but I will probably find it when I go back through the code behind to clean it up.

answered on 10 Apr 2019, 03:12 PM

Leading-zeros-Excel-and-CSV-VB.zip

Hi Geo,

I have attached a working sample to my response. You can download and import the page into a Web Site project, no additional coding is required. Test the application and inspect the code to see how it is configured. This sample demonstrates of keeping leading zeros for both Excel (Html based) and CSV.

I am afraid that I would require a sample project that produces the error to be able to help you further on this case. For that purpose, you can use to edit my sample, then send it back. We would gladly take a look.

Kind regards,
Attila Antal
Progress Telerik

Get quickly onboarded and successful with your Telerik and/or Kendo UI products with the Virtual Classroom free technical training, available to all active customers. Learn More.